Re: Return Record

Rory Campbell-Lange 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.
>

You can't return type "record" in the ways you were trying (RETURN (0,
0); and RETURN 0;). Also, since you were raising EXCEPTION instead of
NOTICE, the function would never return anyway. See below -- I think it
does what you want:

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 NOTICE ''no email found at fn_e3_person_register'';
       SELECT into recone 0,0;
       RETURN recone;
   END IF;

   IF pass IS NULL THEN
       RAISE NOTICE ''no pass found at fn_e3_person_register'';
       SELECT into recone 0,0;
       RETURN recone;
   END IF;

   --
   SELECT INTO recone 1,2;
--      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 NOTICE ''no person board combination found at
fn_e3_person_register'';
       SELECT into recone 0,0;
       RETURN recone;
   END IF;

   RETURN recone;
END;
' LANGUAGE 'plpgsql';

regression=# select * from fn_b1_login2('a', null) as (pid int, bid int);
NOTICE:  no pass found at fn_e3_person_register
  pid | bid
-----+-----
    0 |   0
(1 row)

regression=# select * from fn_b1_login2(null, 'b') as (pid int, bid int);
NOTICE:  no email found at fn_e3_person_register
  pid | bid
-----+-----
    0 |   0
(1 row)

regression=# select * from fn_b1_login2('a', 'b') as (pid int, bid int);
  pid | bid
-----+-----
    1 |   2
(1 row)

HTH,
Joe