Thread: Returning large select results from stored procedures

I'm having a little trouble understanding how to do something.  I assume I'm
just missing it in the documentation, so a pointer to relevent docs would
be as welcome as a direct answer.

I have a project in which I'll need to create a number of stored procedures
that are basically wrappers around complex SQL statements.  Along the lines of:

CREATE OR REPLACE FUNCTION expired(anyelement)
RETURNS SETOF anyelement AS '
     SELECT *
         FROM subscription
             INNER JOIN user
                 ON subscription.userID = user.id
         WHERE subscription.expiredate>=$1;
' LANGUAGE SQL;

This is a greatly simplified example, most are far more complex, but it
illustrates the problem.

This query is going to return between 0 and n records, each with many
columns.  I can't seem to grasp how to teach the procedure to return
an arbitrary number of rows with columns from the select statement.

I keep getting these errors:
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Final SELECT must return exactly one column.
CONTEXT:  SQL function "expired" during startup

Obviously, this isn't going to work, as I'm _always_ going to be
returning more than one column.

TIA for any assistance!

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

> This query is going to return between 0 and n records, each with many
> columns.  I can't seem to grasp how to teach the procedure to return
> an arbitrary number of rows with columns from the select statement.
I think you need to read up on Set Returning Functions, or
SRFs, which are quite new to PG.

HTH,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On Thu, 15 Jan 2004, Bill Moran wrote:

> I'm having a little trouble understanding how to do something.  I assume
> I'm just missing it in the documentation, so a pointer to relevent docs
> would be as welcome as a direct answer.
>
> I have a project in which I'll need to create a number of stored
> procedures that are basically wrappers around complex SQL statements.
> Along the lines of:
>
> CREATE OR REPLACE FUNCTION expired(anyelement)
> RETURNS SETOF anyelement AS '

You probably don't want SETOF anyelement here. I would read that as a set
of any one thing at best.  You probably want to be defining a composite
type with create type and return SETOF thattype or returning SETOF record
and defining the type on the select that uses the function.

You can find more information at
http://techdocs.postgresql.org/guides/SetReturningFunctions
or in General Bits (the url to which I cannot remember off hand).

On Thursday 15 January 2004 15:02, Bill Moran wrote:
> I'm having a little trouble understanding how to do something.  I assume
> I'm just missing it in the documentation, so a pointer to relevent docs
> would be as welcome as a direct answer.
>
> I have a project in which I'll need to create a number of stored procedures
> that are basically wrappers around complex SQL statements.  Along the lines
> of:
...
> I keep getting these errors:
> ERROR:  return type mismatch in function declared to return integer
> DETAIL:  Final SELECT must return exactly one column.
> CONTEXT:  SQL function "expired" during startup

Try SETOF subscription for this example - you're not telling it the type of
the result (which in your example is a row from subscription).

--
  Richard Huxton
  Archonet Ltd

Richard Huxton wrote:
> On Thursday 15 January 2004 15:02, Bill Moran wrote:
>
>>I'm having a little trouble understanding how to do something.  I assume
>>I'm just missing it in the documentation, so a pointer to relevent docs
>>would be as welcome as a direct answer.
>>
>>I have a project in which I'll need to create a number of stored procedures
>>that are basically wrappers around complex SQL statements.  Along the lines
>>of:
>
> ...
>
>>I keep getting these errors:
>>ERROR:  return type mismatch in function declared to return integer
>>DETAIL:  Final SELECT must return exactly one column.
>>CONTEXT:  SQL function "expired" during startup
>
> Try SETOF subscription for this example - you're not telling it the type of
> the result (which in your example is a row from subscription).

Thanks to everyone who responded.  For the sake of the archives:

I solved the problem by creating a type (which I called 'expired_type') that
contains all the fields that are returned by the join in the funtion.  The
function definition was then changed to:

CREATE OR REPLACE FUNCTION expired(timestamp)
RETURNS SETOF expired_type AS '
     SELECT *
         FROM subscription
             INNER JOIN user
                 ON subscription.userID = user.id
         WHERE subscription.expiredate>=$1;
' LANGUAGE SQL;

The upshot being:
1) I can't use type 'subscription', becuase the join changes the type
2) I must define a type to return

Overall, it seems as though postgre's stored procedures are very strongly
typed (as opposed to MS-SQL, in which this application was prototyped by
another, which allows you to return a type that is determined at run time,
and actually supports the concept of "combined recordsets" where not all
rows are even of the same type.  Pretty crazy)

So, if anyone every gets terribly bored (yeah, right!) and wants something
to hack on: a new psuedo-type (perhaps called recordset) that would mimic
the combined recordset functionality of MS-SQL would be a mighty cool
feature ;)

--
Bill Moran
Potential Technologies
http://www.potentialtech.com