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.

Hilaryhilbert

Answer 1

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.

Hilaryhilbert

Can you post the full function including the header. This can be much simplified ... – Exploratory

I've added the header and a bit more detail where necessary. – Hilaryhilbert

I think I finally understand what you are doing here now. You might have made it easier with some general explanation for the role of this function. It's not exactly what the question asks for. – Exploratory

Answer 2

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.

Exploratory

This is really impressive, Erwin! With this being my first semester working in SQL in general, it'll take me a bit to work through the whole solution, but I can't wait to try it out. I'll get back to you with whether or not the speed increased at some point. – Hilaryhilbert

@Nuggles: There are quite a few advanced features in this PL/pgSQL function. :) – Exploratory