postgresql PL/pgSQL 中的 EXECUTE...INTO...USING 语句无法执行到记录中？

EXECUTE...INTO...USING statement in PL/pgSQL can't execute into a record?

提问by StephenDolan

I'm attempting to write an area of a function in PL/pgSQL that loops through an hstoreand sets a record's column(the key of the hstore) to a specific value (the value of the hstore). I'm using Postgres 9.1.

我正在尝试在 PL/pgSQL 中编写一个函数区域，该区域循环遍历 anhstore并将记录的列（ 的键hstore）设置为特定值（ 的值hstore）。我正在使用 Postgres 9.1。

The hstorewill look like: ' "column1"=>"value1","column2"=>"value2" '

该hstore会是这样的：' "column1"=>"value1","column2"=>"value2" '

Generally, here is what I want from a function that takes in an hstoreand has a record with values to modify:

通常，这是我想要的函数，该函数接受一个hstore并具有要修改的值的记录：

FOR my_key, my_value IN
    SELECT key,
           value
      FROM EACH( in_hstore )
LOOP
    EXECUTE 'SELECT '
       INTO my_row.my_key
      USING my_value;
END LOOP;

The error which I am getting with this code:

我用这段代码得到的错误：

"myrow" has no field "my_key". I've been searching for quite a while now for a solution, but everything else I've tried to achieve the same result hasn't worked.

"myrow" has no field "my_key". 我一直在寻找解决方案已经有一段时间了，但是我试图达到相同结果的所有其他方法都没有奏效。

采纳答案by StephenDolan

Since I didn't want to have to use any external functions for speed purposes, I created a solution using hstores to insert a record into a table:

由于我不想为了速度目的而使用任何外部函数，因此我创建了一个使用 hstores 将记录插入表中的解决方案：

CREATE OR REPLACE FUNCTION fn_clone_row(in_table_name character varying, in_row_pk integer, in_override_values hstore)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE

my_table_pk_col_name    varchar;
my_key                  text;
my_value                text;
my_row                  record;
my_pk_default           text;
my_pk_new               integer;
my_pk_new_text          text;
my_row_hstore           hstore;
my_row_keys             text[];
my_row_keys_list        text;
my_row_values           text[];
my_row_values_list      text;

BEGIN

-- Get the next value of the pk column for the table.
SELECT ad.adsrc,
       at.attname
  INTO my_pk_default,
       my_table_pk_col_name
  FROM pg_attrdef ad
  JOIN pg_attribute at
    ON at.attnum = ad.adnum
   AND at.attrelid = ad.adrelid
  JOIN pg_class c
    ON c.oid = at.attrelid
  JOIN pg_constraint cn
    ON cn.conrelid = c.oid
   AND cn.contype = 'p'
   AND cn.conkey[1] = at.attnum
  JOIN pg_namespace n
    ON n.oid = c.relnamespace
 WHERE c.relname = in_table_name
   AND n.nspname = 'public';

-- Get the next value of the pk in a local variable
EXECUTE ' SELECT ' || my_pk_default
   INTO my_pk_new;

-- Set the integer value back to text for the hstore
my_pk_new_text := my_pk_new::text;


-- Add the next value statement to the hstore of changes to make.
in_override_values := in_override_values || hstore( my_table_pk_col_name, my_pk_new_text );


-- Copy over only the given row to the record.
EXECUTE ' SELECT * '
        '   FROM ' || quote_ident( in_table_name ) ||
        '  WHERE ' || quote_ident( my_table_pk_col_name ) ||
                   '    = ' || quote_nullable( in_row_pk )
   INTO my_row;


-- Replace the values that need to be changed in the column name array
my_row := my_row #= in_override_values;


-- Create an hstore of my record
my_row_hstore := hstore( my_row );


-- Create a string of comma-delimited, quote-enclosed column names
my_row_keys := akeys( my_row_hstore );
SELECT array_to_string( array_agg( quote_ident( x.colname ) ), ',' )
  INTO my_row_keys_list
  FROM ( SELECT unnest( my_row_keys ) AS colname ) x;


-- Create a string of comma-delimited, quote-enclosed column values
my_row_values := avals( my_row_hstore );
SELECT array_to_string( array_agg( quote_nullable( x.value ) ), ',' )
  INTO my_row_values_list
  FROM ( SELECT unnest( my_row_values ) AS value ) x;


-- Insert the values into the columns of a new row
EXECUTE 'INSERT INTO ' || in_table_name || '(' || my_row_keys_list || ')'
        '     VALUES (' || my_row_values_list || ')';


RETURN my_pk_new;

END
$function$;

It's quite a bit longer than what I had envisioned, but it works and is actually quite speedy.

它比我预想的要长得多，但它确实有效，而且速度非常快。

回答by Erwin Brandstetter

Simpler alternative to your posted answer. Should perform much better.

更简单的替代您发布的答案。性能应该会好很多。

This function retrieves a row from a given table (in_table_name) and primary key value (in_row_pk), and inserts it as new row into the same table, with some values replaced (in_override_values). The new primary key value as per default is returned (pk_new).

此函数从给定的表 ( 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 #= ).* FROM %1$I t WHERE %3$I = ) 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;

Call:

称呼：

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

SQL Fiddle.

SQL小提琴。

• Use regclassas input parameter type, so only valid table names can be used to begin with and SQL injection is ruled out. The function also fails earlier and more gracefully if you should provide an illegal table name.

• Use an OUTparameter (pk_new) to simplify the syntax.

• No need to figure out the next value for the primary key manually. It is inserted automatically and returned after the fact. That's not only simpler and faster, you also avoid wasted or out-of-order sequence numbers.

• Use format()to simplify the assembly of the dynamic query string and make it less error-prone. Note how I use positional parameters for identifiers and strings respectively.

• I build on your implicit assumptionthat allowed tables have a single primary key column of type integer with a column default. Typically serialcolumns.

• Key element of the function is the final INSERT:

  • Merge override values with the existing row using the #=operatorin a subselect and decompose the resulting row immediately.
  • Then you can select only relevant columns in the main SELECT.
  • Let Postgres assign the default value for the PK and get it back with the RETURNINGclause.
  • Write the returned value into the OUTparameter directly.
  • All done in a single SQL command, that is generally fastest.

• 使用regclass作为输入参数的类型，所以只有有效的表名可用来开始和SQL注入排除。如果您应该提供非法表名，该函数也会更早、更优雅地失败。

• 使用OUT参数 ( pk_new) 来简化语法。

• 无需手动计算主键的下一个值。它会自动插入并在事后返回。这不仅更简单、更快捷，还可以避免浪费或乱序的序列号。

• 使用format()简化装配动态查询字符串，使其不易出错。请注意我如何分别为标识符和字符串使用位置参数。

• 我建立在你隐含的假设之上，即允许的表有一个integer 类型的主键列，列 default。通常是serial列。

• 该函数的关键元素是 final INSERT：

  • 使用#=子选择中的运算符将覆盖值与现有行合并，并立即分解结果行。
  • 然后您可以只选择 main 中的相关列SELECT。
  • 让 Postgres 为 PK 分配默认值并用RETURNING子句取回它。
  • 将返回值OUT直接写入参数中。
  • 所有这些都在单个 SQL 命令中完成，这通常是最快的。