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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq