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
