But first, let me post my problem.
I was doing a simple delete operation over some table..
Say for example,
select count(*) from some_table_x where some_col_name = 'some_value'
This query was returning me a value of 1 which meant is exactly one row that matches this clause. I tried firing a delete query like the one below,
delete from some_table_x where some_col_name = 'some_value'
Ofcourse, the output i expected was that the row to get deleted. But instead, i was informed by postgres as "0 rows deleted. Query executed successfully"
This was very annoying as postgres was not throwing any kind of error and my delete query had the 'where' clause same as the select query i used. The only possible place where the problem might haved occured was the triggers. Even constraints can prevent the row from getting deleted. But the delete operation would have intimated me some kindof error.
So, i concluded only triggers may have this issue. When i went through my trigger, it had a code something like this,
CREATE TRIGGER sample_trig BEFORE INSERT OR UPDATE OR DELETE ON some_table_x FOR EACH ROW EXECUTE PROCEDURE "TRGR_SAMPLE"();
And the trigger function was something like,
CREATE OR REPLACE FUNCTION "TRGR_SAMPLE"() RETURNS "trigger" AS $BODY$ BEGIN IF TG_OP='DELETE' THEN -- some operations END IF; IF TG_OP='UPDATE' THEN -- some operations END IF; IF TG_OP='INSERT' THEN -- some operations END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
The trigger was a BEFORE EACH ROW trigger. It will be fired everytime when one of insert or delete or update operation occurs on some_table_x. The bug in this code was that i was returning NEW at the end of the trigger statement. Whereas in oracle, no one need to worry about what a trigger returns, only need to worry about what a trigger does. But not so in Postgres, you have to write a trigger that calls a trigger function which ofcourse should return either NULL or NEW or OLD.
Since a delete operation does not have a NEW RECORD type, returning NEW does not make any sense at all during a delete operation. So, a small addition to the code really solved the issue.
CREATE OR REPLACE FUNCTION "TRGR_SAMPLE"() RETURNS "trigger" AS $BODY$ BEGIN IF TG_OP='DELETE' THEN -- some operations RETURN OLD; END IF; IF TG_OP='UPDATE' THEN -- some operations END IF; IF TG_OP='INSERT' THEN -- some operations END IF; RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
Although the problem and the solution sounded very simple, finding out that sucked up a lot of time as there was no error been thrown by postgres.
