Re: Return Record

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>