pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to return ONE single record from a function in plpgsql?
4+ messages / 2 participants
[nested] [flat]
* how to return ONE single record from a function in plpgsql?
@ 2003-02-23 00:14 Hubert depesz Lubaczewski <depesz@depesz.pl>
0 siblings, 0 replies; 4+ messages in thread
From: Hubert depesz Lubaczewski @ 2003-02-23 00:14 UTC (permalink / raw)
To: 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 ALIAS FOR $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 ALIAS FOR $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"
^ permalink raw reply [nested|flat] 4+ messages in thread
* how to return ONE single record from a function in plpgsql?
@ 2003-02-23 10:47 Hubert depesz Lubaczewski <depesz@depesz.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Hubert depesz Lubaczewski @ 2003-02-23 10:47 UTC (permalink / raw)
To: 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 ALIAS FOR $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 ALIAS FOR $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"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to return ONE single record from a function in plpgsql?
@ 2003-02-23 16:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hubert depesz Lubaczewski <depesz@depesz.pl>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-02-23 16:34 UTC (permalink / raw)
To: depesz@depesz.pl; +Cc: pgsql-sql
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-DECLAR...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to return ONE single record from a function in plpgsql?
@ 2003-02-27 15:53 Hubert depesz Lubaczewski <depesz@depesz.pl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Hubert depesz Lubaczewski @ 2003-02-27 15:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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"
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-27 15:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-23 00:14 how to return ONE single record from a function in plpgsql? Hubert depesz Lubaczewski <depesz@depesz.pl>
2003-02-23 10:47 how to return ONE single record from a function in plpgsql? Hubert depesz Lubaczewski <depesz@depesz.pl>
2003-02-23 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 15:53 ` Hubert depesz Lubaczewski <depesz@depesz.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox