Postgres Pro
Downloads
Home   >   mailing lists

Thread: Error near delete in plpgsql function

Error near delete in plpgsql function

From
Saqib Ilyas
Date:
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

Re: Error near delete in plpgsql function

From
Alan Hodgson
Date:
On June 6, 2011 08:58:49 AM Saqib Ilyas wrote:
>         END LOOP

> 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

missing semi-colon at end of line 9 (the first END LOOP).


--
Obama has now fired more cruise missiles than all other Nobel Peace prize
winners combined.

Re: Error near delete in plpgsql function

From
Tom Lane
Date:
Saqib Ilyas <msaqib@gmail.com> writes:
> 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?

You forgot to put a semicolon after the first END LOOP.

            regards, tom lane

Re: Error near delete in plpgsql function

From
Andreas Kretschmer
Date:
Saqib Ilyas <msaqib@gmail.com> wrote:

> 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;
 $$


change the ' to $$


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°