From Bart.Degryse@indicator.be Tue Sep 4 10:05:34 2007 Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D5E329F9925 for ; Tue, 4 Sep 2007 07:05:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 81731-02 for ; Tue, 4 Sep 2007 07:05:13 -0300 (ADT) X-Greylist: delayed 01:01:29.03316 by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id CF15A9F994A for ; Tue, 4 Sep 2007 07:05:28 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Tue, 04 Sep 2007 10:46:42 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Tue, 04 Sep 2007 11:03:57 +0200 Message-Id: <46DD3B8E.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Tue, 04 Sep 2007 11:03:43 +0200 From: "Bart Degryse" To: Subject: Use of delete...returning in function problem Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part93B4057F.0__=" X-imss-version: 2.048 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/28 X-Sequence-Number: 29241 --=__Part93B4057F.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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; =20 I get following error though: ERROR: syntax error at or near "delete" at character 4 =20 QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) = returning *) CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5 =20 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:=20 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) =20 So probably I'm doing something wrong. Can anyone tell me what? Thanks, Bart =20 In case it matters: CREATE TABLE "public"."billing_errors_new" ( "billingid" INTEGER NOT NULL,=20 "errortypeid" INTEGER NOT NULL,=20 CONSTRAINT "billing_errors_new_billingid_fkey" FOREIGN KEY ("billingid"= ) REFERENCES "public"."billing"("id") ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,=20 CONSTRAINT "billing_errors_new_errortypeid_fkey" FOREIGN KEY ("errortyp= eid") REFERENCES "public"."billing_error_types"("id") ON DELETE NO ACTION ON UPDATE CASCADE NOT DEFERRABLE ) WITH (fillfactor =3D 100, OIDS =3D FALSE); =20 CREATE UNIQUE INDEX "billing_errors_new_unq" ON "public"."billing_errors_= new" USING btree ("billingid", "errortypeid") WITH (fillfactor =3D100); =20 billingid errortypeid 118075 1 118076 1 118077 1 118078 1 213774 4 336717 4 349906 4 =20 --=__Part93B4057F.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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_err= ors_new;
BEGIN
    FOR rec IN (
  =     delete from billing_errors_new where errortypeid IN = (1,2) returning *)
LOOP
    = RAISE NOTICE 'billingid: % - errortypeid: %', rec.billingid, rec.errortypei= d;
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

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, UPDAT= E, or DELETE with a RETUR= NING clause.
(see http:= //www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQ= L-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" (
  &nb= sp; "billingid" INTEGER NOT NULL,
"errortypeid" INTEGER = NOT NULL,
    CONSTRAINT "billing_errors_new_billingid_f= key" FOREIGN KEY ("billingid")
    REFERENCES = "public"."billing"("id")
    ON DELETE CASCADE
=       ON UPDATE CASCADE
  &nbs= p; DEFERRABLE
    INITIALLY DEFERRED,
= CONSTRAINT "billing_errors_new_errortypeid_fkey" FOREIGN KEY = ("errortypeid")
    REFERENCES "public"."billing_e= rror_types"("id")
      ON DELETE NO ACTION
=     ON UPDATE CASCADE
    = NOT DEFERRABLE
) WITH (fillfactor =3D 100, OIDS =3D FALSE);

=

CREATE UNIQUE INDEX "billing_errors_new_unq" ON "public"."billi= ng_errors_new"
USING btree ("billingid", "errortypeid= ")
WITH (fillfactor =3D100);

billingid errortypeid
118075 1
= 118076 1
118077 1
118078 1
= 213774 4
336717 4
349906 4

--=__Part93B4057F.0__=-- From dev@archonet.com Tue Sep 4 10:49:48 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 31DFE9F96AB for ; Tue, 4 Sep 2007 07:49:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 34464-05 for ; Tue, 4 Sep 2007 07:49:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 5203C9F965C for ; Tue, 4 Sep 2007 07:49:45 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 99E466E00E; Tue, 4 Sep 2007 11:45:19 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id ED756274054; Tue, 4 Sep 2007 11:45:18 +0100 (BST) Message-ID: <46DD373E.1080308@archonet.com> Date: Tue, 04 Sep 2007 11:45:18 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: Bart Degryse Cc: pgsql-sql@postgresql.org Subject: Re: Use of delete...returning in function problem References: <46DD3B8E.A3DD.0030.0@indicator.be> In-Reply-To: <46DD3B8E.A3DD.0030.0@indicator.be> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/30 X-Sequence-Number: 29243 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 From Bart.Degryse@indicator.be Tue Sep 4 12:10:20 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6BA489F95DD for ; Tue, 4 Sep 2007 09:10:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 12302-06 for ; Tue, 4 Sep 2007 09:10:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3]) by postgresql.org (Postfix) with ESMTP id D72679F9A6B for ; Tue, 4 Sep 2007 09:10:12 -0300 (ADT) Received: from webaccess.indicator.be ([10.100.1.7]) by interscan.indicator.com with InterScan Message Security Suite; Tue, 04 Sep 2007 13:52:56 +0200 Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise; Tue, 04 Sep 2007 14:10:10 +0200 Message-Id: <46DD6736.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.1 Date: Tue, 04 Sep 2007 14:09:59 +0200 From: "Bart Degryse" To: Subject: Re: Use of delete...returning in function problem References: <46DD3B8E.A3DD.0030.0@indicator.be><46DD373E.1080308@archonet.com> In-Reply-To: <46DD373E.1080308@archonet.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartBF982907.0__=" X-imss-version: 2.048 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.084 tagged_above=0 required=5 tests=AWL=0.267, BAYES_40=-0.185, HTML_MESSAGE=0.001, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 200709/33 X-Sequence-Number: 29246 --=__PartBF982907.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Amazing what a bracket can do :) Thanks for the help. >>> Richard Huxton 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 =20 > QUERY: ( delete from billing_errors_new where errortypeid IN (1,2) = returning *) > CONTEXT: SQL statement in PL/PgSQL function "test_delete" near line 5 > =20 > According to the manual (I think) it should be possible: I think it's just the brackets () - plpgsql's parser isn't terribly=20 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 :=3D 0; FOR r IN DELETE FROM test1 WHERE a % 10 =3D 1 RETURNING * LOOP n :=3D n + 1; END LOOP; RETURN n; END; $$ LANGUAGE plpgsql; SELECT testdel(); ROLLBACK; --=20 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 --=__PartBF982907.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

Amazing what a bracket can do :)

Thanks for the help.

>>> Richard Huxton <dev@archon= et.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 (
>   &n= bsp;   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: &nbs= p; ( 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,10= 0) AS a, 'text for b';

CREATE FUNCTION testdel() RETURNS integer AS = $$
DECLARE
     n integer;
   &= nbsp; r RECORD;
BEGIN
     n :=3D 0;
&nb= sp;   FOR r IN DELETE FROM test1 WHERE a % 10 =3D 1 RETURNING * = LOOP
         n :=3D n + = 1;
     END LOOP;
     = RETURN n;
END;
$$ LANGUAGE plpgsql;

SELECT testdel();

R= OLLBACK;

--
   Richard Huxton
   = Archonet Ltd

---------------------------(end of broadcast)----------= -----------------
TIP 7: You can help support the PostgreSQL project by = donating at

         &n= bsp;      http://www.postgresql.org/about/donate

--=__PartBF982907.0__=-- From tgl@sss.pgh.pa.us Tue Sep 4 15:55:15 2007 Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id BEAF69FA163 for ; Tue, 4 Sep 2007 12:55:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 76620-06 for ; Tue, 4 Sep 2007 12:55:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4371D9FA15A for ; Tue, 4 Sep 2007 12:55:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l84Ft5XR025974; Tue, 4 Sep 2007 11:55:06 -0400 (EDT) To: Richard Huxton cc: Bart Degryse , pgsql-sql@postgresql.org Subject: Re: Use of delete...returning in function problem In-reply-to: <46DD373E.1080308@archonet.com> References: <46DD3B8E.A3DD.0030.0@indicator.be> <46DD373E.1080308@archonet.com> Comments: In-reply-to Richard Huxton message dated "Tue, 04 Sep 2007 11:45:18 +0100" Date: Tue, 04 Sep 2007 11:55:05 -0400 Message-ID: <25973.1188921305@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/39 X-Sequence-Number: 29252 Richard Huxton 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