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 $$
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;
$$ 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
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
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.