PL/pgSQL中的EXECUTE ... INTO ... USING语句无法执行到记录中? [英] EXECUTE...INTO...USING statement in PL/pgSQL can't execute into a record?

问题描述

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

I'm attempting to write an area of a function in PL/pgSQL that loops through an hstore and 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.

hstore如下所示:' "column1"=>"value1","column2"=>"value2" '

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

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

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

此代码出现的错误:

"myrow" has no field "my_key".我一直在寻找解决方案已经有一段时间了，但是我为获得相同结果而进行的其他所有尝试均无效.

"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.

推荐答案

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

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:

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.

这篇关于PL/pgSQL中的EXECUTE ... INTO ... USING语句无法执行到记录中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！