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
