(PostgreSQL trigger function) How to use TG_ARGV as column names of NEW and OLD?

Question

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?

Answer 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);

Answer 2

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;

• Jasen: json isntead of hstore - it's basically the same mechnism.