Обсуждение: Help with a very newbie question...

Поиск
Список
Период
Сортировка

Help with a very newbie question...

От
"Cristian Prieto"
Дата:
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!

Re: Help with a very newbie question...

От
"Andre Schnoor"
Дата:
>>>
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

Re: Help with a very newbie question...

От
Sean Davis
Дата:
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)

Re: Help with a very newbie question...

От
Jan Poslusny
Дата:
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!

Re: Help with a very newbie question...

От
"Cristian Prieto"
Дата:
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)
>

Re: Help with a very newbie question...

От
Jan Poslusny
Дата:
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
>

Re: Help with a very newbie question...

От
"Cristian Prieto"
Дата:
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
>

Re: Help with a very newbie question...

От
Pavel Stehule
Дата:
>
> 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

Re: Help with a very newbie question...

От
Richard Huxton
Дата:
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

Re: Help with a very newbie question...

От
"Cristian Prieto"
Дата:
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
>

Re: Help with a very newbie question...

От
Pavel Stehule
Дата:
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