I looked at all similar questions, but it seems I didn't have enough knowledge to apply them to my issue.
Take a look at the following code. The bottom line is that I have a function that updates the
updated_at field, the field is updated for the table for which the trigger was fired.
CREATE TEMPORARY TABLE types( id SMALLINT GENERATED ALWAYS AS IDENTITY, type TEXT UNIQUE, updated_at TIMESTAMPTZ ); CREATE OR REPLACE FUNCTION update_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER column_was_updated BEFORE INSERT OR UPDATE OF type ON types FOR EACH ROW EXECUTE PROCEDURE update_column(); INSERT INTO types (type) VALUES ('type1'), ('type2'); SELECT * FROM types
I want to do the following: when calling the function from the trigger, pass a column name as an argument to the function to replace the literal column name
updated_at, something like this:
CREATE OR REPLACE FUNCTION update_column() RETURNS TRIGGER AS $$ BEGIN NEW.column_name = now(); RETURN NEW; END; $$ language 'plpgsql'; CREATE TRIGGER column_was_updated BEFORE INSERT OR UPDATE OF type ON types FOR EACH ROW EXECUTE PROCEDURE update_column('column_name');
How can I achieve this?
P.S. I use PostgreSQL v.14.
You can do that. It's simple, too:
-- trigger function CREATE OR REPLACE FUNCTION update_column() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN NEW := json_populate_record(NEW, json_build_object(TG_ARGV, now())); RETURN NEW; END $func$; -- trigger CREATE TRIGGER column_was_updated BEFORE INSERT OR UPDATE OF type ON types FOR EACH ROW EXECUTE FUNCTION update_column('updated_at');
Doesn't mean you should, though. Typically, you either just use a column
CREATE TABLE types ( id smallint GENERATED ALWAYS AS IDENTITY , type text UNIQUE , updated_at timestamptz DEFAULT now() -- here! );
That can be overwritten with input, though, so it's weaker than a trigger overwriting any input.
Or you write a separate function for every trigger. Like the one you already have. Simple enough. Means a separate trigger function for every involved table, but execution is faster without the dynamic element.
There is precious little space between those simple options for the "smart" solution above.
External links referenced by this document: