Forum OpenACS Q&A: Postgres TRIGGER Parameters?

Request notifications

Posted by Randy O'Meara on
This should be very simple but I'm struggling with it... (PG 7.3.2, CVS HEAD, aolserver3_3_1_ad13, on RHL)

I'm creating a common TRIGGER function that needs the *value* of a table column. The name of the table column differs with each table, but its value in each case represents an oacs object_id. From the doc I've found, the TRIGGER function accesses its passed parameters from the TG_ARGV[] array. I have verified that TG_ARGV[0] and TG_ARGV[1] do contain the values I pass. Now, I need the column value that resides in OLD.TG_ARGV[1], or equivalently, TG_ARGV[0].TG_ARGV[1].

I'm defining the TRIGGER as:

create trigger cmd_object_delete_trig
    after delete on cmd for each row
    execute procedure
Where arg0 (in this case 'cmd') is a table name, and arg1 (here 'cmd_id') is a column name.

I'm defining the TRIGGER FUNCTION as (debugging only at this time so I can get the syntax correct):

create or replace function object_delete() returns trigger as '
    raise notice ''TRIGGERED object_delete BEGIN: table=%, column=%'',TG_ARGV[0], TG_ARGV[1];
    raise notice ''TRIGGERED object_delete (CONT): object_id=%'',OLD.TG_ARGV[1];
    -- ^^^^^ Error is bold on line above ^^^^^

    -- Delete directly assigned acs permissions
    -- delete from acs_permissions where object_id = OLD.TG_ARGV[1];

    -- Delete acs object
    -- perform acs_object__delete(OLD.TG_ARGV[1]);

    raise notice ''TRIGGERED object_delete END: returning OK (NULL)'';
    return NULL;
' language 'plpgsql';
As you can see, I'm attempting to access the column's value. I'm presently getting the following error. I have tried several different forms with various (unsuccessful) result.
WARNING:  plpgsql: ERROR during compile of object_delete near line 3
    Error: Ns_PgExec: result status: 7 message:
    ERROR:  parse error at or near "["

Posted by Don Baccus on
Well, TG_ARGV is not a column in your "cmd" table, so it is surely no surprise that "old.tg_argv" returns an error.  What you want is "old.cmd_id" and to do this you need to build the delete query dynamically using string concatentation and then execute it ...