From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Returning large select results from stored procedures |
Date: | 2004-01-15 15:02:13 |
Message-ID: | 4006AB75.3000406@potentialtech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-01-15 15:06:06 | Re: fixed-length row |
Previous Message | John Sidney-Woollett | 2004-01-15 14:58:21 | Re: parse error in function |