From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: Re: Use of delete...returning in function problem
Date: Tue, 04 Sep 2007 14:09:59 +0200
Message-ID: <46DD6736.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <46DD373E.1080308@archonet.com>
References: <46DD3B8E.A3DD.0030.0@indicator.be>
<46DD373E.1080308@archonet.com>
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
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: <46DD6736.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