Triggers and User Defined Trigger Functions

From: Gordan Bobic <gordan(at)bobich(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Triggers and User Defined Trigger Functions
Date: 2005-03-09 11:13:21
Message-ID: 422EDA51.6080903@bobich.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Howard Cole 2005-03-09 11:14:17 Re: postgresql vs mysql performance comparison
Previous Message Martijn van Oosterhout 2005-03-09 10:50:46 Re: Pgsql dynamic statements and null values