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

Question

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.

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

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;

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.

Answer 1

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

CREATE OR REPLACE FUNCTION f_clone_row(in_table_name regclass
                                     , in_row_pk int
                                     , in_override_values hstore
                                     , OUT pk_new int)
  LANGUAGE plpgsql 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]  -- single PK col!
   WHERE  i.indrelid = in_table_name
   AND    i.indisprimary;

   -- Get list of columns excluding PK column
   SELECT INTO _cols  string_agg(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$;

Call:

SELECT f_clone_row('tbl', 1, '"col1"=>"foo_new","col2"=>"bar_new"');

db<>fiddle here
_{Old sqlfiddle}

Use regclass as 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 OUT parameter (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 unquoted strings respectively.

I build on your implicit assumption that allowed tables have a single primary key column of type integer with a column default. Typically serial columns.

Key element of the function is the final INSERT:

• Merge override values with the existing row using the #= operator in 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 RETURNING clause.

• Write the returned value into the OUT parameter directly.

• All done in a single SQL command, that is generally fastest.