By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,775 Members | 1,740 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,775 IT Pros & Developers. It's quick & easy.

Returning large select results from stored procedures

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a

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/guide...rningFunctions
or in General Bits (the url to which I cannot remember off hand).
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #3

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #4

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.