1

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.

3
0

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:

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.

1
  • Erwin, thank you for the great explanation 👍
    – fallincode
    Oct 28 '21 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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