Message-ID: <46DD373E.1080308@archonet.com>
Date: Tue, 04 Sep 2007 11:45:18 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Bart Degryse <Bart.Degryse@indicator.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use of delete...returning in function problem
References: <46DD3B8E.A3DD.0030.0@indicator.be>
In-Reply-To: <46DD3B8E.A3DD.0030.0@indicator.be>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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