postgresql - 在 PostgreSQL 的触发器函数中重用标识符

Question

我正在使用 PostgreSQL 9.2.23。我有一个触发器函数，它有许多语句在格式语句中重用相同的标识符。为简洁起见，我只包含一个执行语句。

CREATE OR REPLACE FUNCTION update_lookup_table() RETURNS trigger AS 
$BODY$ 
    DECLARE 
        arg_key text; 
        arg_desc text; 
        arg_table text; 
    BEGIN 
        arg_key := TG_ARGV[0]; 
        arg_desc := TG_ARGV[1]; 
        arg_table := TG_ARGV[2]; 

        EXECUTE format('DROP TABLE IF EXISTS %s', 
                        quote_ident('temp_' || arg_table));
        EXECUTE format('CREATE TEMPORARY TABLE %s(%I text, %I text)', 
                        quote_ident('temp_' || arg_table), arg_key, arg_desc);
        EXECUTE format('INSERT INTO %s(%I, %I) 
                        SELECT DISTINCT %I, %I 
                        from staging_staff', 
                        quote_ident('temp_' || arg_table), arg_key, arg_desc, 
                        arg_key, arg_desc); 
        EXECUTE format('LOCK TABLE %I IN EXCLUSIVE MODE', 
                        arg_table);
        EXECUTE format('UPDATE %I 
                        SET %I = %s.%I 
                        FROM %s 
                        WHERE %s.%I = %I.%I', 
                        arg_table, 
                        arg_desc, quote_ident('temp_' || arg_table), arg_desc, 
                        quote_ident('temp_' || arg_table), 
                        quote_ident('temp_' || arg_table), arg_key, arg_table, arg_key);
        EXECUTE format('INSERT INTO %I (%I, %I) 
                        SELECT %s.%I, %s.%I 
                        FROM %s 
                        LEFT OUTER JOIN %I ON ( %I.%I = %s.%I ) 
                        WHERE %I.%I IS NULL', 
                        arg_table, arg_key, arg_desc, 
                        quote_ident('temp_' || arg_table), arg_key, quote_ident('temp_' || arg_table), arg_desc, 
                        quote_ident('temp_' || arg_table), 
                        arg_table, arg_table, arg_key, quote_ident('temp_' || arg_table), arg_key, 
                        arg_table, arg_key);

        RETURN NULL; 

    END; 
$BODY$ 
LANGUAGE plpgsql;

这里是更多上下文的触发器:

CREATE Trigger trig_update_staff_code 
AFTER INSERT OR UPDATE ON staging_staff 
EXECUTE PROCEDURE update_lookup_table('staffgroupcode','staffgroupdescription','staff_group'); 

我使用的一些语句很长，而且它们最终变得非常愚蠢，一遍又一遍地重复相同的 3 个标识符。正如您在我的格式声明中看到的那样，我重复使用了 arg_key 和 arg_desc 标识符两次。有没有一种方法可以声明变量并以某种方式传递它们。如?:

    EXECUTE format('INSERT INTO temp_$A($B, $C) SELECT DISTINCT $B, $C from staging_staff, 
                    arg_table, arg_key, arg_desc);

我试过:

DECLARE 
    ...
    temp_table text;
BEGIN 
    ...
    temp_table:= CONCAT('temp_', arg_table);

INSERT INTO temp_table(arg_key, arg_desc) SELECT DISTINCT arg_key, arg_desc from staging_staff

但是 PostgreSQL 不喜欢这种语法。有没有其他人想办法解决这个问题？

谢谢

编辑:我添加了我正在使用的所有语句。以及采纳 Laurenz 的建议并使用 %s 而不是 %I 作为串联表名

Answer 1

您可以使用 positional format specifiers通过索引引用参数:

EXECUTE format(
  'INSERT INTO %1$I(%2$I, %3$I) SELECT DISTINCT %2$I, %3$I FROM staging_staff', 
  'temp_' || arg_table, arg_key, arg_desc
);

正如 Laurenz 指出的那样，%I 说明符的输入应该是一个完整的标识符名称，因此您需要将 temp_ 前缀附加到参数而不是嵌入它在格式字符串中。