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:
Show quoted text
I want to create a view or a sp which returns NULL if nothing is found
and a recordset if the user is foundI 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...
Show quoted text
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:
Show quoted text
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...
Show quoted text
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 | holaAND 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 | holaAND 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...
Show quoted text
Pavel Stehule wrote:
If I did the following:
SELECT * FROM "Users";
I get:userid | passwd
----------+--------
cristian | holaAND 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