Обсуждение: Help with a very newbie question...
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!
>>> 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? <<< END IF; is missing after the ELSE statement. I often make this mistake too. Andre
On Feb 23, 2005, at 6:14 AM, Andre Schnoor wrote: > > 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; > > Almost.... First, note the declaration for the function--slightly different arguments. Then, note the declare section--two new variables there to replace those in the arguments. You need to END IFs everywhere. Otherwise, looks good. Test given below: create table users ( userid varchar, passwd varchar); CREATE TABLE insert into users values('joe','joepass'); INSERT 156196622 1 insert into users values('susan','susanpass'); INSERT 156196623 1 CREATE OR REPLACE FUNCTION sp_getuser(varchar,varchar) RETURNS record AS $$ DECLARE retval RECORD; name_lu ALIAS FOR $1; pass_lu ALIAS FOR $2; BEGIN SELECT INTO retval * FROM Users WHERE userid=name_lu AND passwd=pass_lu; IF NOT FOUND THEN RETURN NULL; ELSE RETURN retval; END IF; END; $$ LANGUAGE plpgsql; CREATE FUNCTION select sp_getuser('joe','joepass'); sp_getuser --------------- (joe,joepass) (1 row) select sp_getuser('joe','notjoepass'); sp_getuser ------------ (1 row)
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!
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) >
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 >
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 >
> > If I did the following: > SELECT * FROM "Users"; > I get: > > userid | passwd > ----------+-------- > cristian | hola > > AND I execute: > SELECT getuser('cristian', 'hola'); > > I get: > getuser > ----------------- > (cristian,hola) > There are two posibilities SELECT getuser('cristian','hola'); SELECT * FROM getuser('cristian','hola') AS (userid varchar, passwd varchar); Regards Pavel
Pavel Stehule wrote: >>If I did the following: >>SELECT * FROM "Users"; >>I get: >> >>userid | passwd >>----------+-------- >> cristian | hola >> >>AND I execute: >>SELECT getuser('cristian', 'hola'); >> >>I get: >> getuser >>----------------- >> (cristian,hola) >> > > There are two posibilities > > SELECT getuser('cristian','hola'); > SELECT * FROM getuser('cristian','hola') AS (userid varchar, passwd > varchar); Or, define your function as ... RETURNS SETOF Users -- Richard Huxton Archonet Ltd
RETURNS SETOF Users gave me: getuser ----------------- (cristian,hola) ----- Original Message ----- From: "Richard Huxton" <dev@archonet.com> To: "Pavel Stehule" <stehule@kix.fsv.cvut.cz> Cc: "Cristian Prieto" <cristian@clickdiario.com>; <pgsql-general@postgresql.org> Sent: Wednesday, February 23, 2005 10:34 AM Subject: Re: [GENERAL] Help with a very newbie question... > Pavel Stehule wrote: >>>If I did the following: >>>SELECT * FROM "Users"; >>>I get: >>> >>>userid | passwd >>>----------+-------- >>> cristian | hola >>> >>>AND I execute: >>>SELECT getuser('cristian', 'hola'); >>> >>>I get: >>> getuser >>>----------------- >>> (cristian,hola) >>> >> >> There are two posibilities >> >> SELECT getuser('cristian','hola'); >> SELECT * FROM getuser('cristian','hola') AS (userid varchar, passwd >> varchar); > > Or, define your function as ... RETURNS SETOF Users > > -- > Richard Huxton > Archonet Ltd >
On Wed, 23 Feb 2005, Cristian Prieto wrote: > RETURNS SETOF Users gave me: > > getuser > ----------------- > (cristian,hola) > Yes, it's ok, but for SETOF function You have to use different style of calling SELECT * FROM getuser(...). If function call in normal centext then SRF function returns only one column. ps