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