pl/pgsql NEW variable substitution
i want to check in a trigger if certain columns are not left empty. The
columns i have to check are stored in another table. How do i do the
following
BEGIN
SELECT INTO col_record * FROM modules WHERE type_module_id =
NEW.type_module_id AND is_afsluit_kolom;
IF NOT FOUND THEN
RAISE EXCEPTION 'geen afsluitkolom gedefinieerd voor type_module % ',
NEW.type_module_id;
ELSE
col_naam := col_record.kolom_naam;
RAISE NOTICE 'kolom to check is % ', col_naam;
--- check if afsluitrecord is empty
IF NEW.col_naam != '' THEN
RETURN NEW;
ELSE
RAISE EXCEPTION 'dit record is afgesloten voor wijzigingen !';
END IF;
END IF;
END;I want col_naam to be evaluated before used with NEW . Now i get a
warning that the table the trigger is written for doesn't has a column
'col_naam'.
jef peeraer
On Sep 27, 2006, at 6:16 AM, jef peeraer wrote:
i want to check in a trigger if certain columns are not left empty.
The columns i have to check are stored in another table. How do i
do the followingBEGIN SELECT INTO col_record * FROM modules WHERE type_module_id = NEW.type_module_id AND is_afsluit_kolom; IF NOT FOUND THEN RAISE EXCEPTION 'geen afsluitkolom gedefinieerd voor type_module % ', NEW.type_module_id; ELSE col_naam := col_record.kolom_naam; RAISE NOTICE 'kolom to check is % ', col_naam; --- check if afsluitrecord is empty IF NEW.col_naam != '' THEN RETURN NEW; ELSE RAISE EXCEPTION 'dit record is afgesloten voor wijzigingen !'; END IF; END IF; END;I want col_naam to be evaluated before used with NEW . Now i get a
warning that the table the trigger is written for doesn't has a
column 'col_naam'.
Try http://www.postgresql.org/docs/8.1/interactive/plpgsql-
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN, though I'm not sure
if it'll work with NEW.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)