主页 / dba / 问题 / 279910
Accepted
s.k
Asked: 2020-11-19 09:41:01 +0800 CST

postgresql触发器函数中不区分大小写的列名

  • 772

我在 PostgreSQL 12 中有一个触发函数，它执行如下操作：

CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
    latcolumn:= (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lat.*'
    );
    loncolumn := (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lon.*'
    );
    EXECUTE 'select $1.' || loncolumn USING NEW INTO lon;
    EXECUTE 'select $1.' || latcolumn USING NEW INTO lat;

    -- do much stuff

    RETURN NEW;

END
$$
LANGUAGE 'plpgsql';

问题是，latandlon列的名称中有大写字母，例如myLatitudeand myLongitude。触发器函数能够检索这些名称，这没问题。

问题出在两个EXECUTE语句中，其中列名似乎变成了小写，如该错误所述（在触发触发器时的基础 QUERY 中）：

ERROR:  column "mylongitude" not found in data type gpspoints
LINE 1: select $1.myLongitude
               ^
QUERY:  select $1.myLongitude
CONTEXT:  PL/pgSQL function my_latlon_function() line 24 at EXECUTE
********** Error **********

ERROR: column "mylongitude" not found in data type gpspoints
SQL state: 42703
Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE

我知道 PostgreSQL 确实需要将名称中包含大写字母的列名括在双引号中。因此，我尝试在两个 EXECUTE 语句中设置双引号，例如：

    EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon;
    EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat;

但是错误保持不变。

如果可能，如何在 PostgreSQL 触发器函数中处理 CamelCase 列名？

如果不是，为什么？

postgresql trigger

2 个回答

  1. Best Answer

    format与%I标识符的占位符一起使用：

    EXECUTE format('select $1.%I', latcolumn) USING NEW INTO lat;
    • 2

  2. 在需要时使用format()（如 Laurenz 演示）或quote_ident(). 看：

    此外，您的触发功能可以像这样更有效：

    CREATE OR REPLACE FUNCTION my_latlon_function()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _latcol text;
   _loncol text;
   _lat numeric;
   _lon numeric;
BEGIN
   SELECT quote_ident(a.attname) INTO _latcol  -- properly quoted!
   FROM   pg_attribute a
   WHERE  a.attrelid = TG_RELID
   AND    a.attname ~* 'lat'
   AND    a.attnum > 0
   AND    NOT a.attisdropped;

   SELECT quote_ident(a.attname) INTO _loncol
   FROM   pg_attribute a
   WHERE  a.attrelid = TG_RELID
   AND    a.attname ~* 'lon'
   AND    a.attnum > 0
   AND    NOT a.attisdropped;
   
   EXECUTE concat('SELECT $1.', _latcol, ', $1.', _loncol)
   USING  NEW
   INTO   _lat, _lon;

   RAISE NOTICE '% / %', _lat, _lon;  -- debug output

   -- do much stuff

   RETURN NEW;
END
$func$;

    db<>在这里摆弄

    （这是假设列名总是明确匹配！？）

    演示quote_ident()。列名已经正确引用，我们可以concat()稍后进行。

    目录表pg_catalog.pg_attribute明显比information_schema.columns. 比较这两个命令的输出以了解原因：

    EXPLAIN SELECT * FROM information_schema.columns;

    EXPLAIN SELECT * FROM pg_catalog.pg_attribute;

    看：

    在这种特殊情况下，我们也可以方便地使用andTG_RELID来代替。TG_TABLE_SCHEMATG_TABLE_NAME

    此外，在 PL/pgSQL 中，单独的分配相对昂贵。尽量减少他们的人数。

    虽然性能差异仍然很小，但每个插入的行都会调用一次此触发器函数。加起来大插入...

    撇开，col ~* 'lon'完全等价于col ~* '.*lon.*'。

    • 2

相关问题