In the function below I do four "select into" actions to check if
particular values already exist in any one of several tables.
Can I reuse the RECORD for each 'select into'? The RECORDs are
presumably empty after each select into, as the function needs FOUND to
be false to continue.
Also, should I "RETURN 0" after a RAISE EXCEPTION?
Thanks
Rory
CREATE OR REPLACE FUNCTION
fn_c1_create_board ( integer, integer, varchar, varchar, varchar) RETURNS INTEGER
AS '
DECLARE
creator ALIAS for $1;
typer ALIAS for $2;
name ALIAS for $3;
email ALIAS for $4;
description ALIAS for $5;
recone RECORD;
rectwo RECORD;
recthree RECORD;
recfour RECORD;
BEGIN
-- more extensive checking to be done in client program
IF creator IS NULL
THEN
RAISE EXCEPTION ''no creator found at fn_c1_create_board'';
END IF;
IF typer IS NULL
THEN
RAISE EXCEPTION ''no typer found at fn_c1_create_board'';
END IF;
IF name IS NULL
THEN
RAISE EXCEPTION ''no name found at fn_c1_create_board'';
END IF;
IF email IS NULL
THEN
RAISE EXCEPTION ''no email found at fn_c1_create_board'';
END IF;
IF description IS NULL
THEN
RAISE EXCEPTION ''no description found at fn_c1_create_board'';
END IF;
-- find all in people and boards who have a name like this one.
-- if found, abort creation
SELECT into recone
n_id
FROM
boards
WHERE
t_name ~* name;
IF FOUND
THEN
RAISE EXCEPTION ''board with same name found at fn_c1_create_board'';
END IF;
SELECT into rectwo
n_id
FROM
people
WHERE
t_nickname ~* name;
IF FOUND
THEN
RAISE EXCEPTION ''person with same nickname found at fn_c1_create_board'';
END IF;
SELECT into recthree
n_id
FROM
people
WHERE
t_email ~* email;
IF FOUND
THEN
RAISE EXCEPTION ''person with same email found at fn_c1_create_board'';
END IF;
SELECT into recfour
n_id
FROM
boards
WHERE
t_email ~* email;
IF FOUND
THEN
RAISE EXCEPTION ''board with same email found at fn_c1_create_board'';
END IF;
-- ok, if we have got here, its ok to make the board!
INSERT INTO
boards
(n_creator, n_type, t_name, t_email, t_description)
VALUES
(creator, typer, name, email, description);
RETURN 1;
END;'
LANGUAGE plpgsql;
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
