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 SETOF anyelement AS -- (we are not returning anything)
$func$
BEGIN
FOR _tbl_in IN EXECUTE
format('SELECT * FROM %s', pg_typeof(_tbl_in))
LOOP
-- 1. do something with rec
-- 2. insert the rec into events_table_out
EXECUTE format('INSERT INTO %s SELECT ($1).*', _tbl_out)
USING _tbl_in;
END LOOP;
END
$func$ LANGUAGE plpgsql;
Call:
SELECT gesio(NULL::t, 't1');
t and t1 being the tables with identical schema.
Using the implicit cursor of a FOR loop instead of you explicit cursor. That's generally preferable.
Major ingredients
A major obstacle to overcome was, that variables inside the function cannot be defined as polymorphic type (anyelement). 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:
- Provides table name.
- Provides table type.
- Serves as loop variable.
Consider this related answer on SO with more details. the most interesting part being the last chapter "Various complete table types".
Aside:
If your computations for 1. do something with rec are not too sophisticated, you may be able to replace the loop with a single dynamic SQL statement, which is typically much faster.