从information_schema中查找PostgreSQL中未知表的特定列中的值

Question

使用 pl/pgsql，我已经将一个更新函数组合在一起，以便在我需要更改的一堆表上运行，但我不知道所有的名称。我所知道的是，它们都有一个主键(可以命名为任何东西(和一个专门称为"元数据"的列。例如，其中一个"未知"命名表可能如下所示：

CREATE TABLE test
(
    gid SERIAL NOT NULL PRIMARY KEY,
    item varchar,
    metadata varchar,
    z_order int,
    foobar int
);

我正在尝试为我更新的每一行返回(由更新前触发器创建(：

history | action    | table_name | column_name| pkey_id | metadata    |
1       | UPDATE    | test       | item       |       1 |          y  |
_____________________________________________________________________ |
2       | UPDATE    | test       | z_order    |       1 |          y  |
_____________________________________________________________________
3       | UPDATE    | test       | foobar     |       1 |          y  |

但是，我在查询中调用特定column_name"元数据"时遇到问题......为了识别pkey_id(可能具有任何列名(，我information_schema使用：

    SELECT
        INTO pkey_column_name
        kcu.column_name
    FROM
        information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
    WHERE
        constraint_type = 'PRIMARY KEY'
    AND
        ccu.table_name=tg_relname;
 /// more code ///
  FOR each_entity IN
     EXECUTE 'SELECT text((' || QUOTE_NULLABLE(OLD) || '::"' || QUOTE_IDENT(tg_table_schema) || '"."' || QUOTE_IDENT(tg_relname) || '")."'  || QUOTE_IDENT(pkey_column_name) || '")
                AS pk_val, 
/// more code ///

但。。。我尝试将特定column_name"元数据"获取到我的执行查询中失败。理想情况下，我想使用 attname 按名称选择它......但我运气不好...我有一个每列("each_column"(名称(包括"元数据"(的列表，但没有办法在 EXECUTE 语句中调用它......

FOR each_column IN
    SELECT
        attname as column_name
    FROM
        pg_attribute
    WHERE
        attrelid =
        (
            SELECT
                oid --  'oid' is the internal Postgresql primary key used for various system tables
            FROM
                pg_class
            WHERE
                relname = tg_relname -- 'tg_relname' is a special Trigger variable that gives the name of the table invoking the trigger
        )
    AND attnum > 0 -- returns no system columns 
LOOP

Answer 1

而不是：

FOR column_name IN SELECT ...

用：

SELECT ARRAY(SELECT ...) INTO variable

然后使用带有适当quote_ident()调用的 array_to_string()，或者如果你有条件地需要它或 where 子句，则与以下内容相同：

array_to_string((select array(... from unnest(variable) cols where ...)), ',')

这应该允许您根据需要构建任意数量的 select 或 where 子句(使用 ' AND ' 而不是 ','(，并在其上具有任意数量的潜在条件。

Answer 2

这就是我想出来的(感谢丹尼斯的建议(~

http://www.sqlfiddle.com/#!15/64e41/2/0