From: | "Dave Bolt" <dave(at)davebolt(dot)co(dot)uk> |
---|---|
To: | <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Stored Procedure to Delete Rows and Return Count |
Date: | 2018-08-29 21:46:02 |
Message-ID: | 00c701d43fe1$ae3c83e0$0ab58ba0$@co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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) 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, 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.
Thanks
Dave
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan S. Katz | 2018-08-29 21:50:29 | Re: Stored Procedure to Delete Rows and Return Count |
Previous Message | Olivier Leprêtre | 2018-08-29 13:48:36 | redundant constraint_schema |