Thread: plpgsql: returning multiple named columns from function *simply*

Hi guys,

First time (I think, certainly recently) posting to this mailing list.
I've been casually using Postgres for a couple of years, but recently am
going to be using it in a professional settings, so I figure it's time
to get serious about learning the right way to write functions/stored
procedures.

In the past year, I've spent a lot of time writing MS SQL Server stored
procedures, which are pretty good as they go, and I'm essentially trying
to port some actual procedures and my skill set at writing those to
Postgres.  I'm finding it a bit difficult as there are (of course) some
pretty fundamental differences.

I think I've handled most of the important ones so far, but there's one
that's been bugging me.  In MSSQL, I can write a stored procedure that
does something like this:

CREATE PROCEDURE test(
  @lookup char(50))
WITH ENCRYPTION AS BEGIN

-- ... a bunch of code to do some lookup, and then ...

SELECT
   @Result1 AS Result1,
   @Result2 AS Result2,
   @Result3 AS Result3,
   @Result4 AS Result4

END
GO

and then when I call this procedure, I get a result row (like it came
from a SELECT on a table) which has the columns neatly labeled with
'Result1', 'Result2', etc.  Note that these column labels are rather
arbitrary and not necessarily associated with a single table or perhaps
even any existing column in a table.

The question is, how can I best (most easily and elegantly) handle this
in plpgsql?  I've spent a few hours researching it and it seems like you
either use a single (or set of) record or composite types.  The only way
I could get it to work with records though, was to specify the layout of
the row on the actual call to the plpgsql function, which is
undesirable.  And if I use a composite type, I'm going to have to setup
a separate composite type for every special return tuple I might want
out of a function (I do this in a few different places, want to return
perhaps 2-4 *named* columns as results from a function call).

I hope I've made what I'm looking to do clear.  Is there any slick way
of handling this that allows the column naming to occur completely
within the function itself and perhaps doesn't require an external table
or composite type definition?

Thanks,

John Lawler

CREATE PROCEDURE test(

>  @lookup char(50))
> WITH ENCRYPTION AS BEGIN
>
> -- ... a bunch of code to do some lookup, and then ...
>
> SELECT
>   @Result1 AS Result1,
>   @Result2 AS Result2,
>   @Result3 AS Result3,
>   @Result4 AS Result4
>
> END
> GO
>
> and then when I call this procedure, I get a result row (like it came
> from a SELECT on a table) which has the columns neatly labeled with
> 'Result1', 'Result2', etc.  Note that these column labels are rather
> arbitrary and not necessarily associated with a single table or
> perhaps even any existing column in a table.

I think what you are looking for is SetOF functions.

http://www.postgresql.org/docs/8.0/interactive/functions-srf.html

Joshua D. Drake wrote:
>> perhaps even any existing column in a table.
>
> I think what you are looking for is SetOF functions.
> http://www.postgresql.org/docs/8.0/interactive/functions-srf.html

Thanks for the response.

The reference you indicated is talking about Set Returning Functions.
I'm looking to return multiple *columns* from a function, not rows.

Plus, the main part was to be able to have the columns (arbitrarily)
named as if they'd been selected from a table.  I hope that there's
something about as easy as the example I cited from MS SQL.

you can do this with a function that returns a refcursor.
(lookup refcursor in the docs)

you would call it something like this

select mycursorfunct();
fetch all from return_cursor;

In this example I hardcode the name return cursor and then call both
lines from a transaction.

you could also retrieve the name of the cursor into a variable, then do
something like(this is delphi code)

connection.starttransaction;
try
query1.sql.add('select mycursorfunct();');
query1.open;
refcursorname:= query1.fieldbyname('mycursofunct').asstring;
query1.close;
query1.sql.add('fetch all from '+refcursorname);
query1.open;


finally
     connection.commit;
end;


You won't be able to do it exactly like M$ SQL server, but you can do
something equivelent with a couple extra lines of code.

A refcursor takes a couple of more lines of code on the client, but you
don't have to use a type or a record.

If you need a actual test function, let me know.


hope this helps,

Tony Caduto
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql 8.x

>
>
> CREATE PROCEDURE test(
>  @lookup char(50))
> WITH ENCRYPTION AS BEGIN
>
> -- ... a bunch of code to do some lookup, and then ...
>
> SELECT
>   @Result1 AS Result1,
>   @Result2 AS Result2,
>   @Result3 AS Result3,
>   @Result4 AS Result4
>
> END
> GO
>
>
>

John Lawler wrote:
> In MSSQL, I can write a stored procedure that
> does something like this:
>
> CREATE PROCEDURE test(
>   @lookup char(50))
> WITH ENCRYPTION AS BEGIN
>
> -- ... a bunch of code to do some lookup, and then ...
>
> SELECT
>    @Result1 AS Result1,
>    @Result2 AS Result2,
>    @Result3 AS Result3,
>    @Result4 AS Result4

Not sure if this a function like this does everything you may want, but it may work for you.
Disclaimer: I have not actually used the proc with the "As" for the column names,
but I'd expect it "should" work.

CREATE FUNCTION "getlogdata"("int4", "int4", "int4", "int4") RETURNS "refcursor" AS '
 DECLARE curs refcursor;
BEGIN
  OPEN curs FOR
  SELECT logdata.datavalue As Result1,logdata.timestamp As Result2
                 from logdata
                where logfielddatatype = $1
                 and graphtargetlog = $2
                 and (timestamp >=  $3 OR $3 = 0)
                 and (timestamp <=   $4 OR $4 = 0)
                order by timestamp;
 RETURN curs;
END;
' LANGUAGE 'plpgsql';

John Lawler <postgresql.org@tgice.com> writes:
> Plus, the main part was to be able to have the columns (arbitrarily)
> named as if they'd been selected from a table.  I hope that there's
> something about as easy as the example I cited from MS SQL.

In existing releases you need to create a named composite type (row
type) and declare the function as returning that type.  PG 8.1 will
provide some syntactic sugar for this in the form of OUT parameters.
There are examples in the docs ...

            regards, tom lane