By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

create function for trigger question

P: n/a


I am a postgres newbie.

I am trying to create a trigger that will put a copy of a record into a
backup table before update or delete. As I understand it, in order to do
this I must have a function created to do this task.
The function I am trying to create is as follows:

CREATE FUNCTION customer_bak_proc(integer) RETURNS boolean as
'INSERT INTO customer_bak (SELECT * from customer where id = $1 )'
LANGUAGE 'SQL';

Whenever I try to create this function, I get an error on the return type,
as follows:
ERROR: function declared to return boolean, but final statement is not a
SELECT

I have tried using text, integer, opaque, NULL, 0, 1.
And I have tried omitting the "RETURNS" clause altogether, but none of
these works.

What is the return data type of an SQL INSERT statement? I think that
would work (?), but I have not been able to find this in any of the
documentation.

Has anyone else successfully written a function to do an insert?
Has anyone else written a trigger to accomplish this kind of task?
Perhaps I am taking the wrong approach...
Thank you for any help.
Barb Lindsey


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Barbera

What you have written is a stored procedure not a trigger function.
Trigger functions until 7.2 return generally OPAQUE and after 7.2 they
return TRIGGER. See the example of a trigger below for your function

CREATE OR REPLACE FUNCTION customer_bak_proc() RETURNS TRIGGER AS '
BEGIN
IF TG_OP = ''DELETE'' THEN
INSERT INTO customer_bak (id,name,value) VALUES
(OLD.id,OLD.name,OLD.value);
RETURN NULL;
END IF;

IF TG_OP = ''UPDATE'' THEN
INSERT INTO customer_bak (id,name,value) VALUES
(NEW.id,NEW.name,NEW.value);
RETURN NEW;
END IF;
RETURN NULL;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER customer_bak_trigger AFTER DELETE OR UPDATE ON customers
FOR EACH ROW EXECUTE PROCEDURE customer_bak_proc();

What happens above is create the function then create the trigger on the
table and call the function based on what action occurred.

The function is in PL/PGSQL and it basically checks which operation and
then checks the OLD array if it is a delete operation or the NEW array
if it is an update operation.

NEW / OLD will hold all of the variables in the row that you just
updated / deleted and will access them via name and the dot (.) operator.

HTH
Darren
Barbara Lindsey wrote:
I am a postgres newbie.

I am trying to create a trigger that will put a copy of a record into a
backup table before update or delete. As I understand it, in order to do
this I must have a function created to do this task.
The function I am trying to create is as follows:

CREATE FUNCTION customer_bak_proc(integer) RETURNS boolean as
'INSERT INTO customer_bak (SELECT * from customer where id = $1 )'
LANGUAGE 'SQL';

Whenever I try to create this function, I get an error on the return type,
as follows:
ERROR: function declared to return boolean, but final statement is not a
SELECT

I have tried using text, integer, opaque, NULL, 0, 1.
And I have tried omitting the "RETURNS" clause altogether, but none of
these works.

What is the return data type of an SQL INSERT statement? I think that
would work (?), but I have not been able to find this in any of the
documentation.

Has anyone else successfully written a function to do an insert?
Has anyone else written a trigger to accomplish this kind of task?
Perhaps I am taking the wrong approach...
Thank you for any help.
Barb Lindsey


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.