Обсуждение: plpgsql and INSERT/UPDATE/DELETE RETURNING

Jonah's patch for INSERT/etc RETURNING included changes to allow the
RETURNING data to be assigned to plpgsql variables, similar to plpgsql's
version of SELECT INTO.  For instance, collect a serial column's
assigned value with
declare x int;begin  insert into mytab (data) values ($1) returning id into x;

The thing that's bothering me about this is that the behavior is not
well-defined if the RETURNING command operates on more than one row.
It's not clear which row gets returned, and there's also the problem
that the current implementation may stop the executor short after
one row, leaving us with the same partial-execution hazards that
I was on about with respect to execution under V3-protocol portals.

We have a precedent now with the recently added STRICT option for
SELECT INTO.  With STRICT you get an error if the SELECT doesn't return
exactly one row.  Without STRICT, you get the old behavior: nulls if
the SELECT returns no rows, and a randomly-chosen one of the possible
rows if the SELECT would return more than one row.  I propose a slight
modification of those rules for the RETURNING constructs:

Without STRICT: you get nulls if no rows, the values if exactly one
row is affected, an error if more than one row would be affected.

With STRICT: you get an error unless exactly one row is affected.

This prevents the incomplete-execution problem.

BTW, some googling indicates that Oracle's equivalent PL/SQL construct
supports only the exactly-one-row case.  But they have an alternative,
which is that you can use aggregate functions in the RETURNING list and
get a single-row result that is aggregated across all affected rows.
It's too late to consider implementing that for 8.2, I fear, but I think
maybe we should put it on the TODO list for later.

Comments?
        regards, tom lane

Tom Lane wrote:
> With STRICT: you get an error unless exactly one row is affected.
> 
> This prevents the incomplete-execution problem.
> 
> BTW, some googling indicates that Oracle's equivalent PL/SQL construct
> supports only the exactly-one-row case.  But they have an alternative,
> which is that you can use aggregate functions in the RETURNING list and
> get a single-row result that is aggregated across all affected rows.
> It's too late to consider implementing that for 8.2, I fear, but I think
> maybe we should put it on the TODO list for later.

Interesting, an aggregate to combine the rows.  Remembering how much
trouble/discussion the STRICT was, I am unsure if we should add this to
the TODO unless we get someone who likes it.  ;-)

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

On Sun, Aug 13, 2006 at 03:54:18PM -0400, Tom Lane wrote:
> which is that you can use aggregate functions in the RETURNING list and
> get a single-row result that is aggregated across all affected rows.
> It's too late to consider implementing that for 8.2, I fear, but I think
> maybe we should put it on the TODO list for later.

Aggregates sound interesting, though I'm not sure how useful they'd
actually be. I think something like

FOR v_row IN (UPDATE ... RETURNING ...)

would be a lot more useful (if it's not already in the patch).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Aggregates sound interesting, though I'm not sure how useful they'd
> actually be. I think something like
>    FOR v_row IN (UPDATE ... RETURNING ...)
> would be a lot more useful (if it's not already in the patch).

It's not.  I thought about it for a bit but there are some nasty gotchas
if the planner decides it needs to rescan the subquery multiple times.
I'd say that's something to leave for 8.3 ...
        regards, tom lane