Thread: 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