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