Subqueries failing inside pl/pgsql fuction called by trigger

[bob_bamber@hotmail.com (Bob)]

Hi,

I have a very odd postgresql problem.

I have some subqueries contained within a function which are looking
for unrefernced data and then delete any rows that are found:

CREATE OR REPLACE FUNCTION housekeeping()
  RETURNS TRIGGER AS '
  BEGIN
  DELETE FROM properties WHERE NOT EXISTS (SELECT property_id FROM
properties_vw WHERE property_id = properties.property_id);
  DELETE FROM promotions_lookup WHERE promotions_lookup.web_end <
CURRENT_DATE;
  DELETE FROM accommodation_page WHERE NOT EXISTS (SELECT
accommodation_page_id FROM accommodation_page_vw WHERE
accommodation_page.accommodation_page_id = accommodation_page_id);
  DELETE FROM location_page WHERE NOT EXISTS (SELECT location_page_id
FROM location_page_vw WHERE location_page.location_page_id =
location_page_id);
  DELETE FROM dining_page WHERE NOT EXISTS (SELECT dining_page_id FROM
dining_page_vw WHERE dining_page.dining_page_id = dining_page_id);
  DELETE FROM meeting_page WHERE NOT EXISTS (SELECT meeting_page_id
FROM meeting_page_vw WHERE meeting_page.meeting_page_id =
meeting_page_id);
  DELETE FROM meeting_specials WHERE NOT EXISTS (SELECT
meeting_specials_id FROM meeting_specials_vw WHERE
meeting_specials.meeting_specials_id = meeting_specials_id);
  DELETE FROM meeting_rooms WHERE NOT EXISTS (SELECT room_id FROM
meeting_rooms_lookup WHERE meeting_rooms_lookup.room_id =
meeting_rooms.room_id);
  DELETE FROM capacity_data WHERE NOT EXISTS (SELECT data_id FROM
capacities WHERE capacities.data_id = capacity_data.data_id);
  DELETE FROM billboard WHERE NOT EXISTS (SELECT billboard_id from
billboard_vw WHERE billboard.billboard_id = billboard_id);
  DELETE FROM images WHERE NOT EXISTS (SELECT image_id FROM images_vw
WHERE images.image_id = image_id);
  DELETE FROM promotions_lookup WHERE NOT EXISTS (SELECT property_id
FROM properties_vw WHERE property_id = properties.property_id);
  RETURN NULL;
  END; '
  LANGUAGE plpgsql;

I then call the function using this trigger:

CREATE TRIGGER a_delete_brand_trigger
  AFTER DELETE
  ON brands FOR EACH ROW
  EXECUTE PROCEDURE housekeeping();

So I am expecting a query like:

DELETE FROM brands WHERE brand_id = 10;

to fire the trigger which executes function containing the delete
statements and this in turn, removes any unreferenced data.

However, I'm finding that not all of my DELETE statements within the
function are being executed.  If I run the statements outside of the
function they work.  Indeed, if I SELECT housekeeping(); all of the
statements are executed.

Can any body see why when the function is called by the trigger only
some of the statements are executed?

DELETE FROM properties WHERE NOT EXISTS (SELECT property_id FROM
properties_vw WHERE property_id = properties.property_id);
DELETE FROM promotions_lookup WHERE promotions_lookup.web_end <
CURRENT_DATE;

The two above ALWAYS work.  The rest seem to be exectued but they do
not effect any rows.

What's going on?

Many thanks,

Rob.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org