Returning records from PL/PgSQL
By Abhijit Menon-Sen <ams@toroid.org>
I needed to write a plpgsql function that inserted rows into some tables and returned the generated primary key values to the caller. I had never needed to return multiple values from a function before, and I ran into a couple of new problems along the way.
This was my first attempt:
create function create_widget(name text, colour int) returns record as $$ declare wid int; cid int; ids record; begin insert into widgets (name) values (name) returning widget_id into wid; insert into widget_colours (widget_id, colour) values (wid, colour) returning colour_id into cid; select wid::int, cid::int into ids; return ids; end; $$ language plpgsql;
When I tried this, I got a syntax error which I eventually understood to mean that my parameters could not have the same names as any columns in the table I was inserting into. Not very surprising, since the inserts look a bit ambiguous even when reading the code. Since parameter names occur only in the function definition and aren't exposed to the caller, I didn't mind working around this by renaming my parameters "p_name" and "p_colour".
What did surprise me was that I had to call the function like this:
select * from create_widget('x', 3) as cw(widget_id int, colour_id int);
In other words, the caller always had to supply a column definition list, and I couldn't (see any way to) specify what I was going to return in the function definition.
I was given two suggestions about how to work around this problem. The first was to use OUT parameters:
create function create_widget(name text, colour int, widget_id OUT int, colour_id OUT int)
But this approach, predictably enough, had the same problem with the names of parameters conflicting with the names of columns, and I was much less willing to force the caller to retrieve and have to use columns named "r_widget_id" or something similar.
With Postgres 8.4 and above, I learned that I could also declare the function as returning a table, which looked exactly like what I had hoped for:
create function create_widget(name text, colour int) returns table (widget_id int, colour_id int)
But to my great disappointment, the conflict with the column names persists even in this form.
In the end, I decided to use "p_" prefixed parameter names, return a record, and name the columns in the caller.