Hi folks
I have the following function in a file called deletescript.sql.
CREATE OR REPLACE FUNCTION delete_items() RETURNS integer AS '
DECLARE
m RECORD;
n RECORD;
BEGIN
FOR m in SELECT item_id from ITEM where owning_colletion=37 LOOP
FOR n in SELECT workflow_id from workflowitem where collection_id=37 and item_id=m.item_id LOOP
delete from tasklistitem where workflow_id=n.workflow_id;
END LOOP
delete from workflowitem where collection_id=37 and item_id=m.item_id;
END LOOP;
delete from item where owning_collection=37;
return 1;
END;
' LANGUAGE plpgsql;
I started psql and loaded the script using \i deletescript.sql. Then I called the function using select delete_items(); I get the following error:
ERROR: syntax error at or near "delete"
CONTEXT: compile of PL/pgSQL function "delete_items" near line 9
ERROR: syntax error at or near "delete"
CONTEXT: compile of PL/pgSQL function "delete_items" near line 9
Any idea why this happened? I've tried searching the web and the archives, but no clue.
Thanks and best regards
--
Muhammad Saqib Ilyas
PhD Student, Computer Science and Engineering
Lahore University of Management Sciences