Thread: how to return ONE single record from a function in plpgsql?

hi
i was thinking about using something like:
CREATE type helper_login as (logged bool, username TEXT, points INT8,
first_login bool, admin bool, last_login timestamptz, can_ask_questions
bool, timeleft INTERVAL);
CREATE OR REPLACE FUNCTION login(TEXT, TEXT, INT8) RETURNS helper_login
AS '
DECLARE        in_username          ALIAS FOR $1;       in_password          ALIAS FOR $2;       in_quiz_id
ALIASFOR $3;       reply helper_login;
 
BEGIN          reply.logged := true;       reply.username := in_username;       reply.points := 0;
reply.first_login:= true;       reply.admin := false;       reply.last_login := now();       reply.can_ask_questions :=
false;      reply.timeleft := ''1 hour''::INTERVAL;       RETURN reply;
 
END;
' LANGUAGE 'plpgsql';

but it doesn't work:
> select login('depesz','dupa','1');
WARNING:  plpgsql: ERROR during compile of login near line 15
ERROR:  return type mismatch in function returning tuple at or near
"reply"

of course the code as it is now is not very usable, but this is just a
test, on how to achieve what i'd like to.

then i tried to make it work as: select * from login(...) and returning
single row:
CREATE OR REPLACE FUNCTION login(TEXT, TEXT, INT8) RETURNS setof
helper_login AS '
DECLARE        in_username          ALIAS FOR $1;       in_password          ALIAS FOR $2;       in_quiz_id
ALIASFOR $3;       reply helper_login;
 
BEGIN          reply.logged := true;       reply.username := in_username;       reply.points := 0;
reply.first_login:= true;       reply.admin := false;       reply.last_login := now();       reply.can_ask_questions :=
false;      reply.timeleft := ''1 hour''::INTERVAL;       RETURN NEXT reply;       RETURN;
 
END;
' LANGUAGE 'plpgsql';
> select * from login('depesz','dupa','1');
WARNING:  plpgsql: ERROR during compile of login near line 15
ERROR:  Incorrect argument to RETURN NEXT at or near "reply"

hmm .. ok. so it has to be record "inside". let's see:
i modified definition from 
reply helper_login;
to
reply record;
this time i got:
> select * from login('depesz','dupa','1');
WARNING:  Error occurred while executing PL/pgSQL function login
WARNING:  line 7 at assignment
ERROR:  record "reply" is unassigned yet - don't know its tuple
structure

so - is there any chance to return one row which i dont get by a more or
less complicated "select" but rather in a computational way?

best regards

hubert depesz lubaczewski



-- 
hubert depesz lubaczewski                              http://www.depesz.pl/
i choose to hate people when they're not polite; bruise me; that's allright.
bananafishbones                                                    "pow wow"

Hubert depesz Lubaczewski <depesz@depesz.pl> writes:
> i was thinking about using something like:
> CREATE type helper_login as (logged bool, username TEXT, points INT8,
> first_login bool, admin bool, last_login timestamptz, can_ask_questions
> bool, timeleft INTERVAL);
> CREATE OR REPLACE FUNCTION login(TEXT, TEXT, INT8) RETURNS helper_login
> AS '
> DECLARE 
>         in_username          ALIAS FOR $1;
>         in_password          ALIAS FOR $2;
>         in_quiz_id           ALIAS FOR $3;
>         reply helper_login;

You need to say  reply helper_login%ROWTYPE;
otherwise plpgsql doesn't realize that this is supposed to be a rowtype
variable.  (That could stand to be improved, but no one's gotten around
to it yet.)  See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES
        regards, tom lane

On Sun, Feb 23, 2003 at 11:34:21AM -0500, Tom Lane wrote:
> You need to say
>       reply helper_login%ROWTYPE;

thanks. i read the doc, but was misled by first line saying:
name tablename%ROWTYPE;

stupid me - i should heve keep on reading.

best regards

depesz

-- 
hubert depesz lubaczewski                              http://www.depesz.pl/
i choose to hate people when they're not polite; bruise me; that's allright.
bananafishbones                                                    "pow wow"