Обсуждение: Stored Procedure to Delete Rows and Return Count

On Aug 29, 2018, at 5:46 PM, Dave Bolt <dave@davebolt.co.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). 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.

On Aug 29, 2018, at 5:50 PM, Jonathan S. Katz <jonathan.katz@excoventures.com> wrote:

On Aug 29, 2018, at 5:46 PM, Dave Bolt <dave@davebolt.co.uk> wrote:

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.