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
Last activity: Nov 20, 2024, 01:03 PM