Find a good solution for update_at on the postgresql backend

[joseferben]

No description provided.

[tmattio]

Hey @Jerben 🙂

I was looking into Sihl (really nice project!!) and stumbled upon this issue, so I thought I'd share this piece of code that I use for updated_at fields:

CREATE OR REPLACE FUNCTION "public"."set_current_timestamp_updated_at"()
RETURNS TRIGGER AS $$
DECLARE
  _new record;
BEGIN
  _new := NEW;
  _new."updated_at" = now();
  RETURN _new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "set_public_users_updated_at"
BEFORE UPDATE ON "public"."users"
FOR EACH ROW
EXECUTE PROCEDURE "public"."set_current_timestamp_updated_at"();

This will set the value of updated_at to now() every time the row is modified.

Not sure this is what you meant, but I hope it helps 😄

[joseferben]

Hey @tmattio, thanks a lot for the kind words and the nice input! 🙂

Once the MariaDB driver supports non-prepared statements, this will be most likely the solution.
paurkedal/ocaml-caqti#42

[tmattio]

Good to know, thanks for the link!

[joseferben]

@tmattio I just realized this is only an issue for Postgres 🙂 Last time I checked the issue for the MariaDB driver doesn't really exist for the Postgres driver, so this works! 👍

[joseferben]

For now we are doing that on application level so it is repo independent.