Highlight

Sql code posted
created at 19 Jan 16:38

Edit | Back
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE
  in_clause ALIAS FOR $1;
  tenant ALIAS FOR $2;
  categoryId     int;
  requestId     bigint;
BEGIN
SET CONSTRAINTS ALL DEFERRED;
  FOR categoryId IN SELECT id FROM category WHERE category_biz = ANY(in_clause) and tenant_ref=tenant order by level desc
  LOOP
    RAISE NOTICE 'Category Id = %', categoryId;
    DELETE FROM art_leafcat_join WHERE cat_ref = categoryId;
    DELETE FROM cat_cat_join WHERE cat_ref = categoryId;
    DELETE FROM cat_cat_join WHERE child_cat_ref = categoryId;
    DELETE FROM art_cat_join WHERE cat_ref = categoryId;
    DELETE FROM prd_cat_join WHERE cat_ref = categoryId;
    DELETE FROM product_blacklist WHERE type >= 3 AND (from_ref = categoryId OR to_ref = categoryId);
    DELETE FROM product_relationship WHERE (from_ref = categoryId AND type >= 2) OR (to_ref = categoryId AND type = 3);
    DELETE FROM product_exploded_relationship WHERE parent_ref NOT IN (SELECT id FROM product_relationship);
    DELETE from request where id in (SELECT id from request where category_ref = categoryId or optional_category_ref = categoryId);
                PERFORM deletecategories(array_agg(category_biz), tenant) FROM category WHERE parent_category_ref = categoryId;
    DELETE FROM category WHERE id = categoryId;
    END LOOP;
  RETURN;
END
1.24 KB in 6 ms with coderay