Returning records from PL/PgSQL

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.