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>
