Thread: Return Record
I'm not clear on how to handle returning a record from a function.
I have planned a function that is handed two strings and returns two
integers. I need to return errors that satisfy the return type. At the
moment my "RETURN 0;" lines result in "return type mismatch..." errors.
Thanks for any help.
Rory
/*
------------------------
SQL FUNCTION FOR
POSTGRES 7.3
------------------------
Function name: . fn_b1_login.sql
Function description: . Given an email address and password
return the person id and personal board id.
Also perform fn_e30_board_hide to turn on
persons profile (person object) by making
it unhidden if necessary.
*/
CREATE OR REPLACE FUNCTION fn_b1_login2
(varchar, varchar) RETURNS record
AS'
DECLARE
email ALIAS for $1;
pass ALIAS for $2;
recone RECORD;
BEGIN
-- more extensive checking to be done in client program
IF email IS NULL THEN
RAISE EXCEPTION ''no email found at fn_e3_person_register'';
RETURN (0, 0);
END IF;
IF pass IS NULL THEN
RAISE EXCEPTION ''no pass found at fn_e3_person_register'';
RETURN 0;
END IF;
--
SELECT INTO recone
p.n_id as nid, b.n_id as bid
FROM
people p, boards b
WHERE
p.t_email = email
AND
p.t_password = pass
AND
p.n_id = b.n_creator
AND
b.n_type = 0;
IF NOT FOUND THEN
RAISE EXCEPTION ''no person board combination found at fn_e3_person_register'';
RETURN 0;
END IF;
RETURN recone;
END;'
LANGUAGE plpgsql;
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
On 06/06/03, Rory Campbell-Lange (rory@campbell-lange.net) wrote: > I'm not clear on how to handle returning a record from a function. > I have planned a function that is handed two strings and returns two > integers. I need to return errors that satisfy the return type. At the > moment my "RETURN 0;" lines result in "return type mismatch..." errors. I've found a userful resource on postgresql.org: "PostgreSQL 7.3 Set Returning Functions": http://techdocs.postgresql.org/guides/SetReturningFunctions However I'm still having trouble living up to the title of the article! The error I'm getting is: temporary=> select fn_b1_login('email@email', 'pass'); WARNING: Error occurred while executing PL/pgSQL function fn_b1_login WARNING: while casting return value to function's return type ERROR: Set-valued function called in context that cannot accept a set /* ------------------------ SQL FUNCTION FOR POSTGRES 7.3 ------------------------ Function name: . fn_b1_login.sql Function description: . Given a persons email address and password return the person id and personal board id. Also perform fn_e30_board_hide to turn on persons profile (person object) by making it unhidden if necessary. ------------------------ CVS . $Id: fn_b1_login.sql,v 1.2 2003/06/02 11:24:29 rory Exp $ ------------------------ */ DROP TYPE loginrec CASCADE; CREATE TYPE loginrec as (nid INTEGER, bid INTEGER); CREATE OR REPLACE FUNCTION fn_b1_login (varchar, varchar) RETURNS setof loginrec AS' DECLARE email ALIAS for $1; pass ALIAS for $2; recone RECORD; resulter loginrec%rowtype; BEGIN -- more extensive checking to be done in client program IF email IS NULL THEN RAISE EXCEPTION ''no email found at fn_e3_person_register''; -- RETURN (0, 0); END IF; IF pass IS NULL THEN RAISE EXCEPTION ''no pass found at fn_e3_person_register''; -- RETURN 0; END IF; -- SELECT INTO recone p.n_id as nid, b.n_id as bid FROM people p, boards b WHERE p.t_email = email AND p.t_password = pass AND p.n_id = b.n_creator AND b.n_type = 0; IF NOT FOUND THEN RAISE EXCEPTION ''no person board combination found at fn_e3_person_register''; END IF; resulter.nid := recone.nid; resulter.bid := recone.bid; RETURN; END;' LANGUAGE plpgsql; -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
On Fri, 6 Jun 2003, Rory Campbell-Lange wrote: > On 06/06/03, Rory Campbell-Lange (rory@campbell-lange.net) wrote: > > I'm not clear on how to handle returning a record from a function. > > I have planned a function that is handed two strings and returns two > > integers. I need to return errors that satisfy the return type. At the > > moment my "RETURN 0;" lines result in "return type mismatch..." errors. > > I've found a userful resource on postgresql.org: > > "PostgreSQL 7.3 Set Returning Functions": > http://techdocs.postgresql.org/guides/SetReturningFunctions > > However I'm still having trouble living up to the title of the article! > > The error I'm getting is: > temporary=> select fn_b1_login('email@email', 'pass'); > WARNING: Error occurred while executing PL/pgSQL function fn_b1_login > WARNING: while casting return value to function's return type > ERROR: Set-valued function called in context that cannot accept a set *sigh* temporary=> select * from fn_b1_login('email@email', 'pass'); -- Antti Haapala
Rory Campbell-Lange wrote:
> I'm not clear on how to handle returning a record from a function.
> I have planned a function that is handed two strings and returns two
> integers. I need to return errors that satisfy the return type. At the
> moment my "RETURN 0;" lines result in "return type mismatch..." errors.
>
You can't return type "record" in the ways you were trying (RETURN (0,
0); and RETURN 0;). Also, since you were raising EXCEPTION instead of
NOTICE, the function would never return anyway. See below -- I think it
does what you want:
CREATE OR REPLACE FUNCTION fn_b1_login2(varchar, varchar)
RETURNS record AS'
DECLARE
email ALIAS for $1;
pass ALIAS for $2;
recone RECORD;
BEGIN
-- more extensive checking to be done in client program
IF email IS NULL THEN
RAISE NOTICE ''no email found at fn_e3_person_register'';
SELECT into recone 0,0;
RETURN recone;
END IF;
IF pass IS NULL THEN
RAISE NOTICE ''no pass found at fn_e3_person_register'';
SELECT into recone 0,0;
RETURN recone;
END IF;
--
SELECT INTO recone 1,2;
-- p.n_id as nid, b.n_id as bid
-- FROM
-- people p, boards b
-- WHERE
-- p.t_email = email
-- AND
-- p.t_password = pass
-- AND
-- p.n_id = b.n_creator
-- AND
-- b.n_type = 0;
IF NOT FOUND THEN
RAISE NOTICE ''no person board combination found at
fn_e3_person_register'';
SELECT into recone 0,0;
RETURN recone;
END IF;
RETURN recone;
END;
' LANGUAGE 'plpgsql';
regression=# select * from fn_b1_login2('a', null) as (pid int, bid int);
NOTICE: no pass found at fn_e3_person_register
pid | bid
-----+-----
0 | 0
(1 row)
regression=# select * from fn_b1_login2(null, 'b') as (pid int, bid int);
NOTICE: no email found at fn_e3_person_register
pid | bid
-----+-----
0 | 0
(1 row)
regression=# select * from fn_b1_login2('a', 'b') as (pid int, bid int);
pid | bid
-----+-----
1 | 2
(1 row)
HTH,
Joe
On Friday 06 Jun 2003 4:20 pm, Rory Campbell-Lange wrote: > I'm not clear on how to handle returning a record from a function. > I have planned a function that is handed two strings and returns two > integers. I need to return errors that satisfy the return type. At the > moment my "RETURN 0;" lines result in "return type mismatch..." errors. > CREATE OR REPLACE FUNCTION fn_b1_login2 > (varchar, varchar) RETURNS record ... > IF email IS NULL THEN > RAISE EXCEPTION ''no email found at fn_e3_person_register''; > RETURN (0, 0); > END IF; ... > RETURN recone; OK - I think what you probably want to do is define a new type login_results (CREATE TYPE login_results (n_id int, bid int)) and return that rather than "record". Then, I'd just set both fields of "recone" to null and return that during error checks. It doesn't really matter what values you use since the exception will kill the transaction anyway, but null seems better than 0. -- Richard Huxton