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
?
Best Answer
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).
Call (important!):
t
andt1
being the tables with identical schema.Note that 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
Implicit cursor of a
FOR
loop instead of explicit cursor. That's generally preferable.Polymorphic types
Object identifier types
Dynamic SQL in plpgsql
VALUES
can take a row type directly.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 typet
, which serves three purposes:The value of the first parameter is 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.
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.