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>
