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

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) AS$func$DECLARE   _pk   text;  -- name of PK column   _cols text;  -- list of names of other columnsBEGIN-- Get name of PK columnSELECT INTO _pk  a.attnameFROM   pg_catalog.pg_index     iJOIN   pg_catalog.pg_attribute a ON a.attrelid = i.indrelid                                AND a.attnum   = i.indkey[0]  -- 1 PK col!WHERE  i.indrelid = 't'::regclassAND    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 PKEXECUTE 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 directlyINTO    pk_new;                       -- return new pk directlyEND$func$ LANGUAGE plpgsql;

Call:

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

SQL Fiddle.

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

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 integerLANGUAGE plpgsqlAS $function$DECLAREmy_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 variableEXECUTE ' SELECT ' || my_pk_default   INTO my_pk_new;-- Set the integer value back to text for the hstoremy_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 arraymy_row := my_row #= in_override_values;-- Create an hstore of my recordmy_row_hstore := hstore( my_row );-- Create a string of comma-delimited, quote-enclosed column namesmy_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 valuesmy_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 rowEXECUTE '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.