0

I am trying to create a trigger function in PostgreSQL that will take TG_ARGV[0] and TG_ARGV[1] and use those as column names of NEW and OLD, so that I can pass them in as arguments like this:

CREATE TRIGGER users_hash_image_a
  BEFORE INSERT OR UPDATE OF "image_a" ON "users"
  FOR EACH ROW EXECUTE PROCEDURE hash_image_trigger("image_a", "image_a_md5");

My function is this, but it doesn't work:

CREATE FUNCTION hash_image_trigger() RETURNS TRIGGER AS
  $$
  BEGIN
    new.TG_ARGV[1] = MD5(new.TG_ARGV[0]);
    RETURN new;
  END
  $$
  LANGUAGE plpgsql;

How can I use TG_ARGV as column names of NEW and OLD?

2 Answers 2

1

This is a little bit tricky in PL/pgSQL. To get the value from a dynamically-determined column, you can use EXECUTE, as so:

DECLARE
    _md5val text;
BEGIN
    EXECUTE format('SELECT MD5(($1).%I)', TG_ARGV[0]) USING NEW INTO _md5val;
END;

However, to assign to a dynamically determined column, cannot so easily be done. For that, I commonly use the hstore extension from the contrib module.

The hstore extension has an anyelement #= hstore opeatator, so that you can do something like:

NEW := NEW #= hstore(TG_ARGV[1], _md5val);
1

Here's what worked, without needing hstore, but I don't know if it's the "correct" way to do it, or if there's anything unsafe:

CREATE FUNCTION hash_image_trigger() RETURNS TRIGGER AS
  $$
  DECLARE
    image bytea := row_to_json(new)->>TG_ARGV[0];
  BEGIN
    new := json_populate_record(new, json_build_object(TG_ARGV[1], MD5(image)));
    RETURN new;
  END
  $$
  LANGUAGE plpgsql;
1
  • json isntead of hstore - it's basically the same mechnism.
    – Jasen
    Commented Aug 3, 2023 at 3:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.