主页 / dba / 问题 / 61271
Accepted
François Beausoleil
Asked: 2014-03-20 11:31:35 +0800 CST

仅给定字段名称，如何访问 NEW 或 OLD 字段？

  • 772

我正在写一个验证触发器。触发器必须验证数组的总和是否等于另一个字段。由于我有很多这种验证的实例，我想编写一个过程并创建多个触发器，每个触发器都有一组不同的字段要检查。

例如，我有以下架构：

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

验证必须确认impressions_count = sum(impressions_graph).

我被卡住了，因为我不知道如何NEW从 plpgsql 中动态访问字段：

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

我尝试通过做来执行动态命令EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0]，但是 PL/PGsql 抱怨 NEW 是一个未知的关系。

我专门针对 PostgreSQL 9.1。

postgresql trigger

1 个回答

  1. Best Answer

    实际上，由于NEW是一个定义良好的复合类型，您可以使用简单明了的属性表示法访问任何列。SQL 本身不允许使用动态标识符（表或列名等）。但是您可以在 PL/pgSQL 函数中使用动态 SQL 。EXECUTE

    演示

    CREATE OR REPLACE FUNCTION trg_demo1()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text;
   _col_name  text := quote_ident(TG_ARGV[0]);  -- escape identifier
BEGIN
   EXECUTE format('SELECT ($1).%s::text', _col_name)
   USING NEW
   INTO  _col_value;

   -- do something with _col_value ...

   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

    演员表text是可选的。使用它，因为它普遍适用。如果您知道类型，则无需强制转换即可工作...

    使用format()with %s，因为此时标识符已被转义。
    否则，使用format()with%I来防止 SQL 注入。

    或者，在 Postgres 9.3 或更高版本中，您可以将列转换NEW为 JSON，to_json()并将列作为键访问：

    CREATE OR REPLACE FUNCTION trg_demo2()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text := to_json(NEW) ->> TG_ARGV[0];  -- no need to escape identifier
BEGIN
   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

    由于列名没有串联成 SQL 字符串，因此无法进行 SQL 注入，并且不需要对名称进行转义。

    db<>fiddle here（使用EXCEPTION而不是NOTICE使效果可见）。

    有关的：

    • 18

相关问题