Re: [GENERAL] Triggers and User Defined Trigger Functions

[Richard Huxton]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly