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