In my table
userid | passwd ----------+-------- cristian | hola
If I did the following: SELECT * FROM "Users"; I get:
userid | passwd ----------+-------- cristian | hola
But If I create the following Function CREATE OR REPLACE FUNCTION getuser(varchar, varchar) RETURNS SETOF RECORD AS $body$ SELECT * FROM "Users" WHERE userid=$1 AND passwd=$2; $body$ LANGUAGE SQL;
AND I execute:
SELECT getuser('cristian', 'hola');I get:
getuser
-----------------
(cristian,hola)What is wrong? I need to return the data as a simple SELECT * FROM "Users" but inside a SP, how could I do it?
----- Original Message ----- From: "Jan Poslusny" <pajout@xxxxxxxxxxxx>
To: "Cristian Prieto" <cristian@xxxxxxxxxxxxxxx>
Cc: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Wednesday, February 23, 2005 8:36 AM
Subject: Re: Help with a very newbie question...
1. Plpgsql-function can return record (or set of records). Sql-function returns result of the last query in it's body.
2.
I prefer simple solutions, though I write sql-functions whenever requirement likes as "parametrized view". But I know that in some special situations plpgsql-function with clever loops may have better performance than simple sql-function with very complicated joins.
3. Could you explain your question it in some example?
Cristian Prieto wrote:
Well, I will ask a little more clear:
1. A function could be declare to return a RECORD value? (the manual doesn't put it so clear, mention only simple return values and declaration values, not return values).
2. It will be better declare that function as a parametrized view or as a pgsql function?
3. If the record value is not a right return value, what kind of return value could I use for it?
Thanks a lot for your help...
----- Original Message ----- From: "Jan Poslusny" <pajout@xxxxxxxxxxxx> To: "Cristian Prieto" <cristian@xxxxxxxxxxxxxxx> Cc: <pgsql-general@xxxxxxxxxxxxxx> Sent: Wednesday, February 23, 2005 6:44 AM Subject: Re: Help with a very newbie question...
I think that sql-functions may serve as parametrized views for you...
http://www.postgresql.org/docs/8.0/static/xfunc-sql.html
Cristian Prieto wrote:
I want to create a view or a sp which returns NULL if nothing is found and a recordset if the user is found
I wrote something like:
CREATE sp_getuser(name, pass) RETURNS record AS
$body$
DECLARE
retval RECORD;
BEGIN
SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass;
IF NOT FOUND THEN
RETURN NULL;
ELSE
RETURN retval;
END;
$body$
LANGUAGE plpgsql;
What is wrong with that function? I guess I could create it as a View but I don't know how to pass parameters in a view, somebody could help me with this?
Thanks a lot!
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
