是否可以使用变量来访问postgresql触发器中的记录列

发布于 2024-09-19 15:24:59 字数 743 浏览 5 评论 0原文

我有一个触发器来更新每个表的时间戳。我使用以下函数：

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() )
  RETURN NEW;
ELSE
  RETURN NULL;
END IF;
END;
$$ LANGUAGE 'plpgsql';

由于所有时间戳列都有不同的名称，我必须为每个表编写一个函数。

我想做这样的事情：

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';

这样我就可以为每个触发器使用相同的函数。但我不知道通过变量NEW.(timestamp_col_name)访问列的正确语法。

这可能吗？以及它是如何完成的？

原文

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() )
  RETURN 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?

分享到QQ

分享到微博