PostgreSQL functions and triggers

Question

I am trying out functions and triggers int postgreSQL, however i am having a problem, when the function is triggered it is giving me an error

ERROR: control reached end of trigger procedure without RETURN

this particular procedure is only executing an insert into command so i do not see why it needs a return

this is the script:

CREATE OR REPLACE FUNCTION forest_aud_func() returns trigger as $tree_stamp$ 
BEGIN
    insert into Audit values('k',124,'l');
END;
$tree_stamp$
LANGUAGE plpgsql;

create trigger forest_aud_ins after insert on forest
for each row execute procedure forest_aud_func()

insert into forest values('Blue',1600,'Malta','Health Ltd')

user330315 · Accepted Answer · 2012-01-10 15:17:41Z

16

The error message tells you all. You need to do a RETURN from the trigger function:

CREATE OR REPLACE FUNCTION forest_aud_func() returns trigger as $tree_stamp$ 
BEGIN
    insert into Audit values('k',124,'l');
    return new;
END;
$tree_stamp$
LANGUAGE plpgsql;

From the manual:

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

answered Jan 10, 2012 at 15:17

user330315

Sign up to request clarification or add additional context in comments.

9 Comments

Karl Over a year ago

ok thanks, did as you said, now its giving me this error: ERROR: duplicate key value violates unique constraint "audit_pk" DETAIL: Key (au_code)=(124) already exists. i cheked in the table and 124 does not already exist any ideas?

user330315 Over a year ago

@Karl: Apparently you have a primary key on the audit table that you violate when inserting the audit values. That problem has nothing to do with writing the trigger.

Karl Over a year ago

insert into forest values('Blue',1600,'Malta','Health Ltd') is what i am inserting, which should result in a trigger insert of insert into Audit values('k',124,'l');

mu is too short Over a year ago

@Karl: That PK problem is something else and probably should be a different question. As an aside, you really should be specifying the columns (insert into audit (...) values (...)) when using INSERT.

Cameron Martin Over a year ago

Does it matter what you return from a trigger function? Will this effect the outcome of the INSERT/DELETE/UPDATE command, or can you just return null from all trigger functions?

|

Collectives™ on Stack Overflow

PostgreSQL functions and triggers

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related