postgresql – EXECUTE … INTO … PL / pgSQL中的USING语句无法

此函数从给定表(in_table_name)和主键值(in_row_pk)中检索一行,并将其作为新行插入到同一个表中,并替换一些值(in_override_values).返回默认的新主键值(pk_new).

CREATE OR REPLACE FUNCTION f_clone_row(in_table_name regclass,in_row_pk int,in_override_values hstore,OUT pk_new int) AS
$func$
DECLARE
   _pk   text;  -- name of PK column
   _cols text;  -- list of names of other columns
BEGIN

-- Get name of PK column
SELECT INTO _pk  a.attname
FROM   pg_catalog.pg_index     i
JOIN   pg_catalog.pg_attribute a ON a.attrelid = i.indrelid
                                AND a.attnum   = i.indkey[0]  -- 1 PK col!
WHERE  i.indrelid = 't'::regclass
AND    i.indisprimary;

-- Get list of columns excluding PK column
_cols := array_to_string(ARRAY(
      SELECT quote_ident(attname)
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = in_table_name -- regclass used as OID
      AND    attnum > 0               -- exclude system columns
      AND    attisdropped = FALSE     -- exclude dropped columns
      AND    attname <> _pk           -- exclude PK column
      ),',');

-- INSERT cloned row with override values,returning new PK
EXECUTE format('
   INSERT INTO %1$I (%2$s)
   SELECT %2$s
   FROM  (SELECT (t #= $1).* FROM %1$I t WHERE %3$I = $2) x
   RETURNING %3$I',in_table_name,_cols,_pk)
USING   in_override_values,in_row_pk -- use override values directly
INTO    pk_new;                       -- return new pk directly

END
$func$LANGUAGE plpgsql;

呼叫：

SELECT f_clone_row('t',1,'"col1"=>"foo_new","col2"=>"bar_new"'::hstore);

SQL Fiddle.

>使用regclass作为输入参数类型,因此只能使用有效的表名开头并排除SQL注入.如果您应该提供非法的表名,该函数也会更早,更优雅地失败.
>使用OUT参数(pk_new)来简化语法.
>无需手动确定主键的下一个值.它会自动插入并在事后返回.这不仅更简单,更快,还可以避免浪费或无序的序列号.
>使用format()简化动态查询字符串的组装,使其不易出错.请注意我如何分别使用标识符和字符串的位置参数.
>我建立在您隐含的假设基础上,即允许表具有整数类型的单个主键列,其列默认值.通常为serial列.
>该函数的关键元素是最终的INSERT：

>使用子选择中的#= operator将覆盖值与现有行合并,并立即分解生成的行.>然后,您只能选择主SELECT中的相关列.>让Postgres为PK分配默认值,并使用RETURNING子句将其恢复.>直接将返回值写入OUT参数.>所有在一个SQL命令中完成,通常是最快的.