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

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