From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Use of delete...returning in function problem
Date: Tue, 04 Sep 2007 11:03:43 +0200
Message-ID: <46DD3B8E.A3DD.0030.0@indicator.be> (raw)
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;
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, UPDATE, or DELETE with a RETURNING clause.
(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);
CREATE UNIQUE INDEX "billing_errors_new_unq" ON "public"."billing_errors_new"
USING btree ("billingid", "errortypeid")
WITH (fillfactor =100);
billingid errortypeid
118075 1
118076 1
118077 1
118078 1
213774 4
336717 4
349906 4
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: Use of delete...returning in function problem
In-Reply-To: <46DD3B8E.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox