how to return ONE single record from a function in plpgsql?
От | Hubert depesz Lubaczewski |
---|---|
Тема | how to return ONE single record from a function in plpgsql? |
Дата | |
Msg-id | 20030223104731.GD11016@depesz.pl обсуждение исходный текст |
Ответы |
Re: how to return ONE single record from a function in plpgsql?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-sql |
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"
В списке pgsql-sql по дате отправления: