From: | "Jonathan S(dot) Katz" <jonathan(dot)katz(at)excoventures(dot)com> |
---|---|
To: | Dave Bolt <dave(at)davebolt(dot)co(dot)uk> |
Cc: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: Stored Procedure to Delete Rows and Return Count |
Date: | 2018-08-29 21:50:29 |
Message-ID: | 46C297DE-6537-4B24-A5A1-D5B97846B175@excoventures.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> On Aug 29, 2018, at 5:46 PM, Dave Bolt <dave(at)davebolt(dot)co(dot)uk> wrote:
>
> I am (unfortunately) using PG 8.4
> I have created a simple stored procedure to delete records from a table but while it deletes the records it does not return the number of rows deleted.
> I experimented and searched The Internet, and came up with this solution.
>
> CREATE OR REPLACE FUNCTION testdel(integer) RETURNS bigint AS $$
> with d as (delete from foo where id=$1 RETURNING *)
> select count(*)
> $$ LANGUAGE SQL;
>
> Unfortunately this thinks that DELETE is a syntax error.
>
> I tried the following SQL statement in phpPgAdmin (found in an answer on stackoverflow.com <http://stackoverflow.com/>). This also conforms to my understanding of WITH Queries, PostgreSQL Documentation 8.4.22
>
> WITH d AS (DELETE FROM foo WHERE id='1' RETURNING *) SELECT count(*);
>
> and the result was a syntax error on the keyword DELETE, as above.
>
> This statement was accepted as working in stackoverflow.com <http://stackoverflow.com/>, but doesn't work for me.
>
> Can someone give me a clue how this can be done and confirm that their solution has been tested so I know it's my end that has the problem, or better yet tell me what I'm doing wrong.
Being able to use a DELETE in a CTE was introduced in PostgreSQL 9.1.
Jonathan
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan S. Katz | 2018-08-29 21:56:14 | Re: Stored Procedure to Delete Rows and Return Count |
Previous Message | Dave Bolt | 2018-08-29 21:46:02 | Stored Procedure to Delete Rows and Return Count |