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 [EMAIL PROTECTED])
