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"
