Re: Return Record
От | Rory Campbell-Lange |
---|---|
Тема | Re: Return Record |
Дата | |
Msg-id | 20030606163807.GA16373@campbell-lange.net обсуждение исходный текст |
Ответ на | Return Record (Rory Campbell-Lange <rory@campbell-lange.net>) |
Ответы |
Re: Return Record
(Antti Haapala <antti.haapala@iki.fi>)
|
Список | pgsql-general |
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>
В списке pgsql-general по дате отправления: