Re: table name firing trigger - Mailing list pgsql-novice

Michael Fuhr wrote:
> On Wed, Aug 11, 2004 at 09:44:42AM +0200, Raphael Bauduin wrote:
snip
>
>>Also, does the function have access to the type of action that fired the
>>trigger?
>>Eg, if I create a trigger after update and insert, is it possible to know
>>if it's an insert or an update that fired the trigger?
>
>
> That would be TG_OP.  See the PL/pgSQL "Trigger Procedures" manual page
> for more info:
>
> http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html
>
> If you're using a language other than PL/pgSQL then see that
> language's trigger documentation.
>
I'm trying to use it in a plpgsql trigger. I want to insert a row in a table named
TG_RELNAME_log so for example customers_log.
I had problems with escaping a string in the query I wanted to perform:

insert into TG_RELNAME_log select new.*,'UPDATE';

I tried it that way:


CREATE FUNCTION "activity_log"() RETURNS trigger AS '
 BEGIN
        RAISE NOTICE ''table = %'',TG_RELNAME;
        insert into TG_RELNAME||''_log''|| select new.*,''UPDATE'';
        return new;
 END;
 ' LANGUAGE 'plpgsql';

but when I do an update, I get this output:
NOTICE:  table = customers
ERROR:  syntax error at or near "$1" at character 14
CONTEXT:  PL/pgSQL function "activity_log" line 3 at SQL statement



With this version;
CREATE FUNCTION "activity_log"() RETURNS trigger AS '
 BEGIN
        RAISE NOTICE ''table = %'',TG_RELNAME;
        insert into TG_RELNAME_log select new.*,''UPDATE'';
        return new;
 END;
 ' LANGUAGE 'plpgsql';
I get this output:
NOTICE:  table = customers
ERROR:  NEW used in query that is not in a rule
CONTEXT:  PL/pgSQL function "activity_log" line 3 at SQL statement


How can I execute a questy in a function that has to insert a string?

Further on, I tried to use TG_OP rather than inserting the 'UPDATE' string, but I also
have problems using new.* (it has to be out of the string, like TG_RELNAME, but I still gets
a problem):

CREATE FUNCTION "activity_log"() RETURNS trigger AS '
 BEGIN
        RAISE NOTICE ''table = %'',TG_RELNAME;
        RAISE NOTICE ''operation = %'',TG_OP;
        EXECUTE ''insert into ''|| TG_RELNAME||''_log select ''||new.*||'',''||TG_OP;
        return new;
 END;
 ' LANGUAGE 'plpgsql';

but I get this:
NOTICE:  table = customers
NOTICE:  operation = UPDATE
ERROR:  NEW used in query that is not in a rule
CONTEXT:  PL/pgSQL function "activity_log" line 4 at execute statement

An example found at http://dev.e-taller.net/dbtree/fractal/02-triggers.sql seems to indicate this should be possible.

I hope I didn't miss anything obvious this time.....
Thanks.

Raph