Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

LOOP
    FETCH recCurs into rec;
    if not found then
      exit;
    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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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 AS
$func$
BEGIN

FOR _tbl_in IN EXECUTE
   format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
   -- do something with record

   EXECUTE format('INSERT INTO %s VALUES $1', _tbl_out)
   USING _tbl_in;
END LOOP;

END
$func$  LANGUAGE plpgsql;

Call (important!):

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

t and t1 being the tables with identical schema.

Major ingredients

A major 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.

Basically 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 simply discarded. Use NULL.

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

SQL Fiddle demo.

Aside:
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.

share|improve this answer
    
+1... I haven't thought in anyelement for that. Seems a pretty simple and good solution. –  MatheusOl Nov 8 '13 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:

DECLARE
    recCurs table_or_type;
...
BEGIN
...
OPEN recCurs FOR EXECUTE ...
...
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):

CREATE EXTENSION hstore;

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:

DECLARE
   recCurs record;
   kv record;
   v_cols text;
   v_vals text;
BEGIN
...
    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_literal(kv.value) || ',';
    END LOOP;
    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.

share|improve this answer
    
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. –  Erwin Brandstetter Nov 8 '13 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 Nov 8 '13 at 11:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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