Skip site navigation (1) Skip section navigation (2)

Re: BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jonathan Hull" <jono(at)fabsoftware(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: BUG #2873: Function that returns an empty set with a 'not null' domain errors in 8.2 but not 8.1
Date: 2007-01-08 15:27:15
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugspgsql-hackers
"Jonathan Hull" <jono(at)fabsoftware(dot)com> 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

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.


			regards, tom lane

In response to


pgsql-hackers by date

Next:From: Luke LonerganDate: 2007-01-08 15:39:21
Subject: Re: table partioning performance
Previous:From: Hannu KrosingDate: 2007-01-08 15:25:17
Subject: Re: (SETOF) RECORD AS complex_type

pgsql-bugs by date

Next:From: Kevin MacdonaldDate: 2007-01-08 18:52:43
Subject: BUG #2875: pgAdmin III docs installed even if app is not
Previous:From: nico frankenDate: 2007-01-08 13:31:16
Subject: BUG #2874: connection erroe

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group