I am developing a user-defined function that takes two arguments:

create or replace function gesio(
    events_table_in regclass,  
    events_table_out regclass)
returns void as $$ ... $$

events_table_in and events_table_out have exactly the same schema.

Simply explained, I loop through the records of events_table_in, manipulate the records and want to append (insert) the manipulated records into events_table_out in the following fashion:

OPEN recCurs FOR execute 
format('SELECT * FROM %s order by session_id, event_time', event_table_in);

    FETCH recCurs into rec;
    if not found then
    end if;

    -- 1. do something with rec

    -- 2. insert the rec into events_table_out

end loop;

How can I save the rec into events_table_out?

4 Answers 4


There is a solution with just PL/pgSQL. Simple and elegant, too. Pretty advanced stuff, though.
Requires Postgres 9.0 or later (workaround for older versions possible).

CREATE OR REPLACE FUNCTION gesio(_tbl_in anyelement, _tbl_out regclass)
  RETURNS void
  LANGUAGE plpgsql AS
   FOR _tbl_in IN EXECUTE
      format('SELECT * FROM %s', pg_typeof(_tbl_in))
      -- do something with record

      EXECUTE format('INSERT INTO %s SELECT $1.*', _tbl_out)
      USING _tbl_in;

Call (important!):

SELECT gesio(NULL::t, 't1');

t and t1 being tables with identical schema.

The polymorphic parameter (anyelement) is only needed if you need it's value or data type for the computation in the function body. Else you can simplify like demonstrated in this later answer:

Major ingredients

An obstacle to overcome is that variables inside the function cannot be defined as polymorphic type anyelement (yet). This related answer on SO explains the solution. Provides a workaround for older versions, too.

I am handing in a NULL value of type t, which serves three purposes:

  • Provide table name.
  • Provide table type.
  • Serve as loop variable.

The value of the first parameter is discarded. Just use NULL.

Consider this related answer on SO with more details. The most interesting part being the last chapter "Various complete table types".

db<>fiddle here
Old sqlfiddle

If your computations are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically faster.

  • +1... I haven't thought in anyelement for that. Seems a pretty simple and good solution.
    – MatheusOl
    Commented Nov 8, 2013 at 11:55

Unfortunately it is not easy to parse the RECORD type using PL/pgSQL. If the structure of the tables passed in arguments are always the same as some other table or type you could use this type directly instead of RECORD, and then use the following:

    recCurs table_or_type;
EXECUTE 'INSERT INTO ' || events_table_out || ' VALUES(($1).*)'
        USING recCurs;

But this will not work with RECORD type. The only solution I can think of is creating the query by hand. But PL/pgSQL gives no way to dynamically get the keys of an RECORD type. So you have to use some external tools. The best (in my opinion) for this kind of job is the hstore extension. Once installed you can create it on your database (the following works only on 9.1+, for earlier you should do it by hand):


Now. You are able to convert a RECORD type to an hstore type, using hstore(recCurs), and so you can dynamically iterate over its keys and values with the each function:

   recCurs record;
   kv record;
   v_cols text;
   v_vals text;
    OPEN recCurs FOR EXECUTE ...
    -- 1. do something with rec

    -- 2. insert the rec into events_table_out:
    v_cols := '';
    v_vals := '';
    FOR kv IN SELECT * FROM each(hstore(recCurs)) LOOP
        v_cols := v_cols || kv.key || ',';
        v_vals := v_vals || quote_nullable(kv.value) || ',';
    v_cols := substr(v_cols, 1, length(v_cols)-1);
    v_vals := substr(v_vals, 1, length(v_vals)-1);
    EXECUTE 'INSERT INTO ' || events_table_out
            || '(' || v_cols || ') '
            || 'VALUES (' || v_vals || ')';

Of course, it will only work if the table "pointed" by events_table_out has all the columns that events_table_in has (the first can have more columns).

RESUMING: always you want some dynamic key/value data-type on PL/pgSQL, and RECORD is not enough, the hstore can be used.

  • hstore is generally good advice and your answer is pretty useful (+1). It just so happens that there is a simpler solution for the case. Commented Nov 8, 2013 at 0:58
  • @ErwinBrandstetter, your answer is (with no doubt) really better, simpler and probably more performatic. I'll keep my answer here, because it does solve the problem, not the best way though.
    – MatheusOl
    Commented Nov 8, 2013 at 11:58
  • The quote_literal in v_vals := v_vals || quote_literal(kv.value) || ','; inside the LOOP should be quote_nullable, otherwise the whole EXECUTE become NULL in case of NULL values in the record.
    – gabrimonfa
    Commented Dec 18, 2015 at 13:46
  • @gabrimonfa, you are correct. fixed
    – MatheusOl
    Commented Dec 20, 2015 at 13:50

I needed to do something similar and ended up using the INSERT from SELECT. I need to duplicate records in the same table but changing one field. Maybe it's not the most ellegant solution but it works.

CREATE OR REPLACE FUNCTION public.duplicar_mascotas(
    origen integer DEFAULT 0, 
    destino integer DEFAULT 0
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
  fila_mascota record;
  nvo_id_mascota BIGINT;
  contador integer;
  cur_mascotas cursor for
    SELECT id FROM public.mascotas WHERE id_persona = origen;
    contador := 0;
    raise notice 'hola';
    open cur_mascotas;
        fetch cur_mascotas into fila_mascota;
        exit when not found;
        -- insert del select
        INSERT INTO public.mascotas ( nombre, id_persona, edad_meses) 
        SELECT nombre, destino as id_persona, edad_meses
        FROM public.mascotas
            id = fila_mascota.id;
        contador := contador + 1;
    end loop;
    return contador;

Then, i call the function like so:

select duplicar_mascotas (1,2);

This duplicates the records from the person with id = 1 to the person with id = 2.


You can use any of the sql commands below:

/* variant 1 */
insert into test_history select v_history_row.*;
/* variant 2 */
execute 'insert into test_history SELECT $1.*' using v_history_row;
  • 2
    For future visitors, please dpread it out and explain it. Commented Jul 19, 2023 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.