Damn triggers and NEW - Mailing list pgsql-general

Okay, I'm obviously doing something wrong but what?

I have a trigger defined as:


create or replace function cms_user_id_insert_tgr_1 ( )
    returns trigger
    as '
    begin
        execute ''
        select 1
            from
                individuals i
                ,user_roles ur
                ,roles r
            where
                    r.name = ''''CMS''''
                and
                    ur.role_id = r.id
                and
                    ur.individual_id = i.id
                and
                    i.id = NEW.'' || quote_ident(TG_ARGV[0]) || ''
                and
                    i.active is true'';
        if not found then
            raise exception ''TRIGGER: UserNotFound'';
        end if;

        return NEW;
    end;
    '
    language 'plpgsql';

create trigger cms_user_id_insert before insert
    on groups
    for each row
    execute procedure cms_user_id_insert_tgr_1 ( 'principal_user_id' );



And when this trigger gets kicked I get a message about NEW used in a non-rule
query. The postmaster log is below.

I'd appreciate some pointers on this as it appears new/old can't be used in an
execute statement in triggers but that sounds completely wrong.


--
Nigel J. Andrews



2003-06-17 10:09:08 LOG:  query: insert into ttacms1.workflow_events (
                article_id,
                after_status_id,
                xml_changes,
                event_dt,
                notes,
                before_status_id,
                user_id,
                id
        )
(select
                article_id,
                after_status_id,
                xml_changes,
                                        ur.role_id = r.id
                                and
                                        ur.individual_id = i.id
                                and
                                        i.id = NEW.' || quote_ident( $1 ) || '
                                and
                                        i.active is true'
2003-06-17 10:27:50 LOG:  query: SELECT 0
2003-06-17 10:27:50 LOG:  query:
                select 1
                        from
                                individuals i
                                ,user_roles ur
                                ,roles r
                        where
                                        r.name = 'CMS'
                                and
                                        ur.role_id = r.id
                                and
                                        ur.individual_id = i.id
                                and
                                        i.id = NEW.principal_user_id
                                and
                                        i.active is true
2003-06-17 10:27:50 ERROR:  NEW used in non-rule query