Thread: Use of delete...returning in function problem
I'm trying to use a delete statement with returning clause in a function:
CREATE OR REPLACE FUNCTION "public"."test_delete"() RETURNS void AS
$body$
DECLARE
rec billing_errors_new;
BEGIN
FOR rec IN (
delete from billing_errors_new where errortypeid IN (1,2) returning *)
LOOP
RAISE NOTICE 'billingid: % - errortypeid: %', rec.billingid, rec.errortypeid;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
$body$
DECLARE
rec billing_errors_new;
BEGIN
FOR rec IN (
delete from billing_errors_new where errortypeid IN (1,2) returning *)
LOOP
RAISE NOTICE 'billingid: % - errortypeid: %', rec.billingid, rec.errortypeid;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I get following error though:
ERROR: syntax error at or near "delete" at character 4
QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) returning *)
CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5
QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) returning *)
CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5
According to the manual (I think) it should be possible:
The query used in this type of FOR statement can be any SQL command that returns rows to the caller:
SELECT is the most common case, but you can also use INSERT, UPDATE, or DELETE with a RETURNING clause.
(see http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING)
(see http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING)
So probably I'm doing something wrong. Can anyone tell me what?
Thanks,
Bart
In case it matters:
CREATE TABLE "public"."billing_errors_new" (
"billingid" INTEGER NOT NULL,
"errortypeid" INTEGER NOT NULL,
CONSTRAINT "billing_errors_new_billingid_fkey" FOREIGN KEY ("billingid")
REFERENCES "public"."billing"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
DEFERRABLE
INITIALLY DEFERRED,
CONSTRAINT "billing_errors_new_errortypeid_fkey" FOREIGN KEY ("errortypeid")
REFERENCES "public"."billing_error_types"("id")
ON DELETE NO ACTION
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH (fillfactor = 100, OIDS = FALSE);
"billingid" INTEGER NOT NULL,
"errortypeid" INTEGER NOT NULL,
CONSTRAINT "billing_errors_new_billingid_fkey" FOREIGN KEY ("billingid")
REFERENCES "public"."billing"("id")
ON DELETE CASCADE
ON UPDATE CASCADE
DEFERRABLE
INITIALLY DEFERRED,
CONSTRAINT "billing_errors_new_errortypeid_fkey" FOREIGN KEY ("errortypeid")
REFERENCES "public"."billing_error_types"("id")
ON DELETE NO ACTION
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH (fillfactor = 100, OIDS = FALSE);
CREATE UNIQUE INDEX "billing_errors_new_unq" ON "public"."billing_errors_new"
USING btree ("billingid", "errortypeid")
WITH (fillfactor =100);
USING btree ("billingid", "errortypeid")
WITH (fillfactor =100);
billingid errortypeid
118075 1
118076 1
118077 1
118078 1
213774 4
336717 4
349906 4
118075 1
118076 1
118077 1
118078 1
213774 4
336717 4
349906 4
Bart Degryse wrote: > I'm trying to use a delete statement with returning clause in a function: > FOR rec IN ( > delete from billing_errors_new where errortypeid IN (1,2) returning *) > LOOP > I get following error though: > ERROR: syntax error at or near "delete" at character 4 > QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) returning *) > CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5 > > According to the manual (I think) it should be possible: I think it's just the brackets () - plpgsql's parser isn't terribly sophisticated. This works for me, but with brackets doesn't. BEGIN; CREATE TEMPORARY TABLE test1 (a integer, b text); INSERT INTO test1 SELECT generate_series(1,100) AS a, 'text for b'; CREATE FUNCTION testdel() RETURNS integer AS $$ DECLARE n integer; r RECORD; BEGIN n := 0; FOR r IN DELETE FROM test1 WHERE a % 10 = 1 RETURNING * LOOP n := n + 1; END LOOP; RETURNn; END; $$ LANGUAGE plpgsql; SELECT testdel(); ROLLBACK; -- Richard Huxton Archonet Ltd
Amazing what a bracket can do :)
Thanks for the help.
>>> Richard Huxton <dev@archonet.com> 2007-09-04 12:45 >>>
Bart Degryse wrote:
> I'm trying to use a delete statement with returning clause in a function:
> FOR rec IN (
> delete from billing_errors_new where errortypeid IN (1,2) returning *)
> LOOP
> I get following error though:
> ERROR: syntax error at or near "delete" at character 4
> QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) returning *)
> CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5
>
> According to the manual (I think) it should be possible:
I think it's just the brackets () - plpgsql's parser isn't terribly
sophisticated.
This works for me, but with brackets doesn't.
BEGIN;
CREATE TEMPORARY TABLE test1 (a integer, b text);
INSERT INTO test1 SELECT generate_series(1,100) AS a, 'text for b';
CREATE FUNCTION testdel() RETURNS integer AS $$
DECLARE
n integer;
r RECORD;
BEGIN
n := 0;
FOR r IN DELETE FROM test1 WHERE a % 10 = 1 RETURNING * LOOP
n := n + 1;
END LOOP;
RETURN n;
END;
$$ LANGUAGE plpgsql;
SELECT testdel();
ROLLBACK;
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
>>> Richard Huxton <dev@archonet.com> 2007-09-04 12:45 >>>
Bart Degryse wrote:
> I'm trying to use a delete statement with returning clause in a function:
> FOR rec IN (
> delete from billing_errors_new where errortypeid IN (1,2) returning *)
> LOOP
> I get following error though:
> ERROR: syntax error at or near "delete" at character 4
> QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) returning *)
> CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5
>
> According to the manual (I think) it should be possible:
I think it's just the brackets () - plpgsql's parser isn't terribly
sophisticated.
This works for me, but with brackets doesn't.
BEGIN;
CREATE TEMPORARY TABLE test1 (a integer, b text);
INSERT INTO test1 SELECT generate_series(1,100) AS a, 'text for b';
CREATE FUNCTION testdel() RETURNS integer AS $$
DECLARE
n integer;
r RECORD;
BEGIN
n := 0;
FOR r IN DELETE FROM test1 WHERE a % 10 = 1 RETURNING * LOOP
n := n + 1;
END LOOP;
RETURN n;
END;
$$ LANGUAGE plpgsql;
SELECT testdel();
ROLLBACK;
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Richard Huxton <dev@archonet.com> writes: > I think it's just the brackets () - plpgsql's parser isn't terribly > sophisticated. It's not plpgsql's fault --- you'll get the same result if you put parentheses around a DELETE command at the SQL command line. regression=# (delete from fool); ERROR: syntax error at or near "delete" LINE 1: (delete from fool); ^ The OP may be used to putting parens into his FOR loops because it works with SELECT: regression=# (select * from zz1);f1 | f2 | f3 ----+----+---- (0 rows) The difference is that SELECT can be put into larger groupings (eg UNIONs) so it has to be parenthesiz-able. If we ever considered supporting DELETE RETURNING as a component of larger queries, this syntax difference would likely go away. regards, tom lane