Hey thanks for your email, this was exactly the explanation I was looking for. I figured out the CREATE TYPE technique but I'm gonna give the out parameters a try as well, it kinda looks cleaner especially if the only thing that uses the type is a single stored proc..
Albe Laurenz wrote:
Mike Christensen wrote:
I have the following function:
CREATE FUNCTION foo(_userid uuid) RETURNS SETOF record AS
$BODY$
BEGIN RETURN QUERY select n.UserId, u.Alias, n.Date, n.Data --Bunch of joins, etc
If I understand correctly, I have to return "SETOF record" since my
result set doesn't match a table and isn't a single value. However,
this means when I want to call it I have to provide a column definition
list, such as:
select * from foo as (...);
Is there any way to specify this column list within the function
itself? The problem I'm running into is I want to call this function
using Npgsql which doesn't appear to support passing in a column
definition list.
You can avoid that problem if you specify the return type in the function definition.
There are two possibilities:
The "classical" way is to define a TYPE similar to this:
CREATE TYPE foo_type AS ( _userid uuid, _alias text, _date date, _data text
);
or similar, depending on your select list and data types.
Then you can define the function as:
CREATE FUNCTION foo(_userid uuid) RETURNS SETOF foo_type ...
The "new" way is to use output parameters. This is a little harder
to understand, but you need not define a foo_type:
CREATE FUNCTION foo(INOUT _userid uuid, OUT _alias text, OUT _date date, OUT _data text) RETURNS SETOF RECORD ...
In both cases you can call the function like this:
SELECT * FROM foo('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11');
The OUT parameters are just a different way of specifying the output type.
Yours,
Laurenz Albe
