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__=--