Thread: Using results from RETURNING

Hi all,

When RETURNING from a DELETE statement (or similar), how do you access the
results being returned?

Something like:

SELECT COUNT(*) FROM (DELETE FROM a RETURNING *) ;

sounds reasonable but results in a syntax error. I am able to return single
results into a variable or record, but not more than one result.

I could use a SELECT ... FOR UPDATE in the meantime, I just wanted to know
what the use of RETURNING * was if you can't do anything with the results.

Thanks!

Shak

Shak wrote:


> Something like:
>
> SELECT COUNT(*) FROM (DELETE FROM a RETURNING *) ;
>
> sounds reasonable but results in a syntax error. I am able to return
> single results into a variable or record, but not more than one result.

Subqueries using DELETE ... RETURNING or UPDATE ... RETURNING are not
yet supported. Unfortunately. It'd be extremely useful in some
situations, but it sounds like there are plenty of issues that make it
less than trivial.

> I could use a SELECT ... FOR UPDATE in the meantime, I just wanted to
> know what the use of RETURNING * was if you can't do anything with the
> results.

You can use them in most situations other than subqueries. PL/PgSQL
SELECT INTO, returning to the client, etc. Really handy.

--
Craig Ringer