开发者

Is it possible to use variables to access columns of records in postgresql trigger

开发者 https://www.devze.com 2023-01-16 05:53 出处:网络
I have a trigger to update my timestamps for each table. I use the following function: CREATE OR REPLACE FUNCTION update_timstamp_table0() RETURNS TRIGGER AS

I have a trigger to update my timestamps for each table. I use the following function:

CREATE OR REPLACE FUNCTION update_timstamp_table0() RETURNS TRIGGER AS
$$
BEGIN
IF NEW IS DISTINCT FROM OLD THEN
  NEW.table0_timestamp_column = extract( 'epoch' from NOW() )
  RETU开发者_StackOverflow中文版RN NEW;
ELSE
  RETURN NULL;
END IF;
END;
$$ LANGUAGE 'plpgsql';

Since all the timestamp columns have different names i have to write a function for each table.

I'd like to do something like this:

CREATE OR REPLACE FUNCTION update_timstamp(timestamp_col_name varchar) RETURNS TRIGGER AS
$$
BEGIN
IF NEW IS DISTINCT FROM OLD THEN
  NEW.(timestamp_col_name) = extract( 'epoch' from NOW() )
  RETURN NEW;
ELSE
  RETURN NULL;
END IF;
END;
$$ LANGUAGE 'plpgsql';

So i can use the same function for every trigger. But i don't know the right syntax for accessing the column via a variable NEW.(timestamp_col_name).

Is this possible? and how it is done?


Take a look at this question: How to get the key fields for a table in plpgsql function?

0

精彩评论

暂无评论...
验证码 换一张
取 消