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