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