[GENERAL] pl/pgsql NEW variable substitution

[jef peeraer]

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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] pl/pgsql NEW variable substitution

[Jim Nasby]

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 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'.


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[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



---(end of broadcast)---
TIP 6: explain analyze is your friend