Re: Help with a very newbie question...

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(at)gingerall(dot)cz>
To: "Cristian Prieto" <cristian(at)clickdiario(dot)com>
Cc: <pgsql-general(at)postgresql(dot)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(at)gingerall(dot)cz>
>> To: "Cristian Prieto" <cristian(at)clickdiario(dot)com>
>> Cc: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>