Re: Triggers and User Defined Trigger Functions

Richard Huxton wrote:
> Gordan Bobic wrote:
>
>> Hi,
>>
>> I'm trying to figure out how to do this from the documentation, but I
>> can't figure it out. :-(
>>
>> Here is what I'm trying to do:
>>
>> CREATE TABLE MyTable
>> (
>>     ID    bigserial unique,
>>     MyData    char(255),
>>     PRIMARY KEY (ID)
>> );
>>
>> CREATE TABLE Archive_MyTable
>> (
>>     ID    bigserial unique,
>>     MyData    char(255),
>>     PRIMARY KEY (ID)
>> );
>>
>> CREATE FUNCTION MyTable_Trigger_DELETE()
>> RETURNS ???opaque/trigger/HeapTuple??? AS '
>
>
> RETURNS TRIGGER
>
>
> You can't use SQL as the target language, it has to be one of the
> procedural languages (e.g. plpgsql)
>
> Something like:
>
> CREATE FUNCTION my_trig_fn() RETURNS trigger AS '
> BEGIN
>   INSERT INTO archive_mytable (id,mydata) VALUES (OLD.id, OLD.mydata);
>   RETURN OLD;
> END;
> ' LANGUAGE plpgsql;

Thanks. :-)
I did that, and I can now create the function and the trigger OK. But
when the trigger fires (i.e. on DELETE), I get the following error:

DELETE FROM Temp1 WHERE Test = 'test3';
ERROR:  syntax error at or near "$2" at character 44
QUERY:  INSERT INTO Temp2 (ID, test) VALUES ( $1   $2 )
CONTEXT:  PL/pgSQL function "temp1_trigger_delete" line 2 at SQL statement
LINE 1: INSERT INTO Temp2 (ID, test) VALUES ( $1   $2 )

What did I miss?

Gordan