Re: Help with a very newbie question...
От | Cristian Prieto |
---|---|
Тема | Re: Help with a very newbie question... |
Дата | |
Msg-id | 024801c519bc$365cbac0$6e00a8c0@gt.ClickDiario.local обсуждение исходный текст |
Ответ на | Help with a very newbie question... ("Cristian Prieto" <cristian@clickdiario.com>) |
Ответы |
Re: Help with a very newbie question...
(Pavel Stehule <stehule@kix.fsv.cvut.cz>)
|
Список | pgsql-general |
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@gingerall.cz> To: "Cristian Prieto" <cristian@clickdiario.com> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, February 23, 2005 8:36 AM Subject: Re: [GENERAL] 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@gingerall.cz> >> To: "Cristian Prieto" <cristian@clickdiario.com> >> Cc: <pgsql-general@postgresql.org> >> Sent: Wednesday, February 23, 2005 6:44 AM >> Subject: Re: [GENERAL] 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@postgresql.org) >>> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
В списке pgsql-general по дате отправления: