On Mon, Jan 08, 2007 at 10:27:15AM -0500, Tom Lane wrote: > "Jonathan Hull" <[EMAIL PROTECTED]> writes: > > The key feature for the error is that when a result structure (eg : pg_foo) > > is defined with a domain type that is not null, only PG 8.2 errors if the > > result is an empty set. > > The problem is explained well enough by this comment in plpgsql's code > for FOR-over-query: > > /* > * If the query didn't return any rows, set the target to NULL and return > * with FOUND = false. > */ > > At the time this code was written, there weren't any potential negative > side-effects of trying to set a row value to all NULLs, but now it's > possible that that fails because of domain constraints. > > I think the idea was to ensure that a record variable would have the > correct structure (matching the query output) post-loop, even if the > query produced zero rows. But it's not clear that that is really > useful for anything, given plpgsql's dearth of introspection facilities. > So we could make Jonathan's problem go away if we just take out the > assignment of nulls, and say that FOR over no rows leaves the record > variable unchanged. The documentation doesn't specify the current > behavior. > > Looking through the code, I see another place that does the same thing: > FETCH from a cursor, when the cursor has no more rows to return. It's > a bit harder to argue that it's sane to leave the variable unchanged > in this case. However, the documentation doesn't actually promise that > the target gets set to null in this case either.
I think it would be safer to set the record variable to something that wipes any old data. I can't think of any examples of good code that would fall prey to this, but I can certainly think of some nasty bugs that users could inadvertently create. I know I'd personally like to have the safety net... Perhaps a means to mark the record as being null, other than setting all the fields to null? That might also allow for a means for users to set records to null, which I think would be useful in some cases. BTW, are row variables also affected by this bug or is it just record variables? -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
