Thread: Triggers and User Defined Trigger Functions

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 '
INSERT INTO Archive_MyTable
(
    ID,
    MyData
)
VALUES
(
    OLD.ID,
    OLD.MyData
);
RETURN OLD;
' LANGUAGE SQL;

This gives me one of the following errors:
ERROR:  SQL functions cannot return type opaque
ERROR:  SQL functions cannot return type "trigger"
ERROR:  type "heaptuple" does not exist

What type should my function be returning?
ERROR: type

Then I'd like to do the following:

CREATE TRIGGER MyTable_Trigger_DELETE BEFORE DELETE ON MyTable
FOR EACH ROW
EXECUTE PROCEDURE MyTable_Trigger_DELETE();

Can I create a trigger function like this? If not, what are my options
WRT alternatives?

Many thanks.

Gordan

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

> INSERT INTO Archive_MyTable
> (
>     ID,
>     MyData
> )
> VALUES
> (
>     OLD.ID,
>     OLD.MyData
> );
> RETURN OLD;
> ' LANGUAGE SQL;

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;

You can also use many other languages for functions - tcl/perl/python (I
think)/java etc. Check your language of choice supports triggers though.

--
   Richard Huxton
   Archonet Ltd

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

> 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?

A comma in the indicated position I guess...

HTH,
Csaba.

Csaba Nagy wrote:
>>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?
>
>
> A comma in the indicated position I guess...

Thanks. I'm feeling really stupid now. You may all mock me. :-)
Thanks for your help, it's most appreciated. :-)

Gordan