Sample Header Ad - 728x90

Column name as argument for a trigger function

3 votes
1 answer
2302 views
In my Postgres 14 database, I have a function that updates the updated_at field in a 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?
Asked by fallincode (133 rep)
Oct 26, 2021, 03:59 PM
Last activity: Nov 20, 2024, 01:03 PM