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