Problem with postgresql function

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
barb woolums
Forum Contributor
Posts: 134
Joined: Sun Feb 08, 2009 9:52 pm

Problem with postgresql function

Post by barb woolums »

I have a function containing lots of delete statements. It runs without errors, but doesn't seem to do anything, as I can run one of the delete statements by itself afterwards and it will delete rows.

Any advice would be appreciated.

Here's the function (created in pgadmin III)

CREATE OR REPLACE FUNCTION query_delete_unused()
RETURNS void AS
$BODY$delete from category_owner where category not in(select distinct cat from recipe_cat_subcat where cat is not null) and id!=20;

delete from category where id not in(select distinct cat from recipe_cat_subcat where cat is not null) and id!=20;

delete from subcategory_owner where subcategory not in(select distinct subcat from recipe_cat_subcat where subcat is not null) and id!=32;

delete from subcategory where id not in(select distinct subcat from recipe_cat_subcat where subcat is not null) and id!=32;

delete from cuisine_owner where cuisine not in(select distinct cuisine from recipe where cuisine is not null);

delete from cuisine where id not in(select distinct cuisine from recipe where cuisine is not null);

delete from diet_owner where diet not in(select distinct diet from recipe_diet where diet is not null);

delete from diet where id not in(select distinct diet from recipe_diet where diet is not null);

delete from measure_owner where measure not in(select distinct measure from recipe where measure is not null);

delete from measure where id not in(select distinct measure from recipe where measure is not null);

delete from ingredient_owner where sl is false and ingredient not in(select distinct ing from recipe_ing where ing is not null);

delete from ingredient where id not in(select distinct ingredient from ingredient_owner where ingredient is not null);

delete from source_owner where source not in(select distinct source from recipe where source is not null);

delete from source where id not in(select distinct source from recipe where source is not null);

delete from unit_owner where unit not in(select distinct unit from recipe_ing where unit is not null);

delete from unit where id not in(select distinct unit from recipe_ing where unit is not null);

delete from yield_unit_owner where yield_unit not in(select distinct yield_unit from recipe where yield_unit is not null and recipe.owner=yield_unit_owner.owner);

delete from yield_unit where id not in(select distinct yield_unit from recipe where yield_unit is not null);

delete from preprep_owner where preprep not in(select distinct preprep1 from recipe_ing where preprep1 is not null and recipe in (select id from recipe where recipe.owner=preprep_owner.owner) union select distinct preprep2 from recipe_ing where preprep2 is not null and recipe in (select id from recipe where recipe.owner=preprep_owner.owner));

delete from preprep where id not in(select distinct preprep1 from recipe_ing where preprep1 is not null union select distinct preprep2 from recipe_ing where preprep2 is not null);$BODY$
LANGUAGE 'sql' VOLATILE
COST 100;
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Problem with postgresql function

Post by Christopher »

I don't remeember the syntax well, but I think you want something like:

CREATE OR REPLACE FUNCTION query_delete_unused()
RETURNS void AS $$
BEGIN
*** deletes here ***
END
(#10850)
User avatar
barb woolums
Forum Contributor
Posts: 134
Joined: Sun Feb 08, 2009 9:52 pm

Re: Problem with postgresql function

Post by barb woolums »

Thanks for your reply. Worked it out.
Post Reply