Column name as an argument of the function called in the trigger (PostgreSQL)

Question

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.

Does this answer your question? Trigger function using current row and current table name as variables (final part) — mustaccio, Oct 26 '21 at 16:28
@mustaccio can you suggest a code that suits my situation? As I said in the question - I looked at all similar questions, but it seems I didn't have enough knowledge to apply them to my issue. — fallincode, Oct 27 '21 at 05:52

Erwin Brandstetter · Accepted Answer · 2021-10-28T20:45:46.617

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[0], 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');

db<>fiddle here

Use the special variable TG_ARGV for trigger functions, and json_populate_record() to set a field dynamically. See:

How to set value of composite variable field using dynamic SQL

Doesn't mean you should, though. Typically, you either just use a column DEFAULT:

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.

Column name as an argument of the function called in the trigger (PostgreSQL)

1 Answers1