Обсуждение: Use of delete...returning in function problem

Поиск
Список
Период
Сортировка

Use of delete...returning in function problem

От
"Bart Degryse"
Дата:
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
 

Re: Use of delete...returning in function problem

От
Richard Huxton
Дата:
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;
RETURNn;
 
END;
$$ LANGUAGE plpgsql;

SELECT testdel();

ROLLBACK;

--   Richard Huxton  Archonet Ltd

Re: Use of delete...returning in function problem

От
"Bart Degryse"
Дата:
Amazing what a bracket can do :)
Thanks for the help.

>>> Richard Huxton <dev@archonet.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 (
>       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

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

                http://www.postgresql.org/about/donate

Re: Use of delete...returning in function problem

От
Tom Lane
Дата:
Richard Huxton <dev@archonet.com> 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