Re: pl/pgsql NEW variable substitution

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 jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)