Re: Return Record

On 06/06/03, Rory Campbell-Lange (rory(at)campbell-lange(dot)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(at)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(at)campbell-lange(dot)net>
<www.campbell-lange.net>