Re: Triggers and User Defined Trigger Functions

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