On Jul 27, 2009, at 5:19 PM, David E. Wheeler wrote:
Yep, that's just what I needed, thanks. I think I'll send a patch
for the "Cursors" section of the PL/pgSQL documentation that
mentions this. Would have saved me a bunch of hassle.
So would have reading two more sentences of the docs, whi
On Jul 26, 2009, at 4:02 PM, Eric B. Ridge wrote:
I'm just a random lurker, but FOUND seems to work just fine (I
suppose it's PG-specific?).
http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
BEGIN
OPEN stuff;
FETCH stuff INTO rec;
WHILE FO
On Mon, Jul 27, 2009 at 9:48 AM, Kevin
Grittner wrote:
> The latter really *is* a form of optimizer hint, it's just an
> undocumented, arcane hint for the Illuminati.
Well said.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
Sam Mason wrote:
> I've heard lots and read a few smaller articles but don't think
> I've got around to any of his books.
Having just poked around on the Internet, I think perhaps this was his
only full-fledge book, per se. The rest of his work appears to have
been papers published in academ
Greg Stark wrote:
> Kevin Grittner wrote:
>> impossible to write two queries which can be shown to be logically
>> equivalent but which optimize to different access plans
>
> Personally I think that's a fine goal to aim for.
Sure, but from my experience, there aren't any database products whi
On Jul 26, 2009, at 6:46 PM, David E. Wheeler wrote:
Is there some way to get using_while() to properly return all the
records?
I'm just a random lurker, but FOUND seems to work just fine (I suppose
it's PG-specific?).
http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPG
On Sun, Jul 26, 2009 at 03:46:19PM -0700, David E. Wheeler wrote:
> And something I'd like to be able to handle in a while loop, as I'm
> actually fetching one row at a time from two cursors and need to be
> able to tell when I've reached the end of a cursor.
I'm sure I'm missing something obv
On Jul 25, 2009, at 4:41 PM, David E. Wheeler wrote:
Useless perhaps, but it's gonna happen, and someone may even have a
reason for it. Until such time as NULLs are killed off, we need to
be able to deal with SQL's pathologies.
And something I'd like to be able to handle in a while loop, as
On Sun, Jul 26, 2009 at 12:49:32PM -0500, Kevin Grittner wrote:
> Codd, E.F. (1990). The Relational Model for Database Management
> (Version 2 ed.). Addison Wesley Publishing Company.
> ISBN 0-201-14192-2.
Looks as though I've got some reading to do then--somewhat annoying that
only second hand co
On Sun, Jul 26, 2009 at 6:49 PM, Kevin
Grittner wrote:
> Also, the
> requirement that, to be considered a relational database, it must be
> impossible to write two queries which can be shown to be logically
> equivalent but which optimize to different access plans to be, well, a
> bit "ivory tower.
Sam Mason wrote:
> I've not read much of his writings, any canonical references for
> this sort of discussion?
I think this is the one, although it's been a while since I read it,
and I might be getting it confused with something else he wrote:
Codd, E.F. (1990). The Relational Model for Da
On Sat, Jul 25, 2009 at 11:42:04AM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > In this dichotomy a NULL is most definitely a value and with my
> > current experience I don't understand the distinction you're trying
> > to draw.
>
> There can be a place where a value *could* go which doe
On Jul 25, 2009, at 9:42 AM, Kevin Grittner wrote:
I know that Codd was insistent that any relation (which included the
result of any query) which could contain duplicate rows should be
called a "corrupted relation". (In fact, in one of his books I think
he averaged a comment on this point abou
On Jul 25, 2009, at 9:24 AM, Jeff Davis wrote:
I don't think you want the "NOT x IS NULL" part at all -- that will
evaluate to false when x = rec(NULL,NULL). I think you just want the
"x
IS DISTINCT FROM NULL" part, right? Will that work?
Nope, infinite loop when because `ROW(null, null)` a
Sam Mason wrote:
> On Fri, Jul 24, 2009 at 12:30:39PM -0500, Kevin Grittner wrote:
> In this dichotomy a NULL is most definitely a value and with my
> current experience I don't understand the distinction you're trying
> to draw.
There can be a place where a value *could* go which does not co
On Fri, 2009-07-24 at 15:47 -0700, David E. Wheeler wrote:
> WHILE (NOT rec_have IS NULL AND rec_have IS DISTINCT FROM NULL)
> OR (NOT rec_want IS NULL AND rec_want IS DISTINCT FROM NULL)
>
I don't think you want the "NOT x IS NULL" part at all -- that will
evaluate to false when x =
"David E. Wheeler" wrote:
> So when it gets to that second row in the first cursor, it doesn't
> know it's a row with NULLs as opposed to an empty row.
> there ought to be an easy way to tell the difference. :-(
I would have thought that the correct thing is to check SQLSTATE for
'02000'.
On Jul 24, 2009, at 2:59 PM, David E. Wheeler wrote:
FETCH have INTO rec_have;
FETCH want INTO rec_want;
WHILE NOT rec_have IS NULL OR NOT rec_want IS NULL LOOP
IF rec_have IS DISTINCT FROM rec_want THEN
RETURN false;
END IF;
rownum = rownum + 1;
F
On Fri, Jul 24, 2009 at 12:30:39PM -0500, Kevin Grittner wrote:
> Sam Mason wrote:
> > The fact that it happens to be a NULL *value* in one case
>
> Well, according to Codd (and I tend to go with him on this) there is
> no such thing. NULL is a way to flag a place where a value could be
> store
On Jul 24, 2009, at 11:37 AM, Jeff Davis wrote:
Hmmm ... that suggests that we may not be handling IS DISTINCT FROM
correctly for rowtypes. I haven't read that part of the spec, but
I would've guessed that the spec wants it to be consistent with
IS NULL.
Our behavior appears to match the stan
On Jul 24, 2009, at 9:40 AM, Jeff Davis wrote:
There's no single test, but you can see if it's an empty row with:
x IS NULL AND x IS DISTINCT FROM NULL
And you can see if it's a "real" NULL by:
x IS NULL AND x IS NOT DISTINCT FROM NULL
It's funny until you try it -- then it's just scary.
Am
On Fri, Jul 24, 2009 at 4:29 PM, Greg Stark wrote:
> On Fri, Jul 24, 2009 at 7:49 PM, Merlin Moncure wrote:
>> Still, why:
>>
>> *) is select null::foo is distinct from (null, null)::foo; true?
>> (null should not be distinct from null)
>>
>> *) does (null, null) allow STRICT functions to execute?
On Fri, Jul 24, 2009 at 7:49 PM, Merlin Moncure wrote:
> Still, why:
>
> *) is select null::foo is distinct from (null, null)::foo; true?
> (null should not be distinct from null)
>
> *) does (null, null) allow STRICT functions to execute?
>
> *) is (null, null)::text not null?
These are all good
On Fri, Jul 24, 2009 at 2:05 PM, Tom Lane wrote:
> Merlin Moncure writes:
>> I think the following should _not_ return true:
>> select (null, null)::somerowtype is null;
>
> Take it up with the SQL standards committee. The spec is 100% not
> ambiguous about this.
I'm not proposing to change the
On Fri, 2009-07-24 at 14:10 -0400, Tom Lane wrote:
> Hmmm ... that suggests that we may not be handling IS DISTINCT FROM
> correctly for rowtypes. I haven't read that part of the spec, but
> I would've guessed that the spec wants it to be consistent with
> IS NULL.
>
Our behavior appears to matc
On Fri, 2009-07-24 at 13:35 -0400, Merlin Moncure wrote:
> I think the following should _not_ return true:
> select (null, null)::somerowtype is null;
>
> The reasoning being that
It's not at all clear to me that you can "reason" effectively about SQL
logic semantics. Upon which laws are you reas
On Jul 24, 2009, at 11:10, Tom Lane wrote:
Hmmm ... that suggests that we may not be handling IS DISTINCT FROM
correctly for rowtypes. I haven't read that part of the spec, but
I would've guessed that the spec wants it to be consistent with
IS NULL.
Yes, that's precisely what I was trying to
On Jul 24, 2009, at 3:17, Brendan Jurd wrote:
"ROW(NULL, NULL) IS NULL" returns true, but that is not the
same as saying that it actually is NULL
I think my head just exploded. Loving the dark corners of SQL…
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
Jeff Davis writes:
> There's no single test, but you can see if it's an empty row with:
> x IS NULL AND x IS DISTINCT FROM NULL
> And you can see if it's a "real" NULL by:
> x IS NULL AND x IS NOT DISTINCT FROM NULL
Hmmm ... that suggests that we may not be handling IS DISTINCT FROM
correctly
Merlin Moncure writes:
> I think the following should _not_ return true:
> select (null, null)::somerowtype is null;
Take it up with the SQL standards committee. The spec is 100% not
ambiguous about this.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-
On Fri, Jul 24, 2009 at 1:35 PM, Merlin Moncure wrote:
> On Fri, Jul 24, 2009 at 5:15 AM, David E. Wheeler wrote:
>> On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
>>
>>> Well, a ROW is an ordered set of values, each one of which may be
>>> either NULL or NOT NULL.
>>
>> Right.
>>
>>> It doesn't
On Fri, Jul 24, 2009 at 5:15 AM, David E. Wheeler wrote:
> On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
>
>> Well, a ROW is an ordered set of values, each one of which may be
>> either NULL or NOT NULL.
>
> Right.
>
>> It doesn't really make sense to talk about the ROW itself being NULL
>> or N
Sam Mason wrote:
> On Fri, Jul 24, 2009 at 09:31:13AM -0400, Tom Lane wrote:
> Hope nobody minds if I go off on a somewhat pedagogic bent here!
Not as long as you don't mind replies in kind. ;-)
> The fact that it happens to be a NULL *value* in one case
Well, according to Codd (and I te
On Fri, Jul 24, 2009 at 09:31:13AM -0400, Tom Lane wrote:
> Brendan Jurd writes:
> > So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't
> > have the property of being NULL or not NULL, because it is a composite
> > value. "ROW(NULL, NULL) IS NULL" returns true, but that is not t
On Fri, 2009-07-24 at 09:31 -0400, Tom Lane wrote:
> Brendan Jurd writes:
> It's worse than that, because there actually is also such a thing as
> the row value being NULL --- ie, there's no row structure at all.
> At least internally, that's a completely different thing from having
> a row all of
Joshua Tolley writes:
> On Thu, Jul 23, 2009 at 06:46:25PM -0700, David E. Wheeler
>> Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I
>> would expect it to be NOT DISTINCT from `ROW(2, NULL)`.
> Wait, didn't we decide upthread that the standard said ROW(1, NULL) isn't
On Thu, Jul 23, 2009 at 06:46:25PM -0700, David E. Wheeler wrote:
> Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I
> would expect it to be NOT DISTINCT from `ROW(2, NULL)`.
Wait, didn't we decide upthread that the standard said ROW(1, NULL) isn't
NULL?
(From Tom):
> Th
Brendan Jurd writes:
> So it is wrong to talk about ROW(NULL, NULL) being NULL. It doesn't
> have the property of being NULL or not NULL, because it is a composite
> value. "ROW(NULL, NULL) IS NULL" returns true, but that is not the
> same as saying that it actually is NULL, because of the diffe
Does this also apply at the column level?
In Postgres is If column IS NOT NULL or If column IS NULL? Do all
columns in the record have to be NULL to have IF column IS NULL = true?
Best Regards
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 f
2009/7/24 David E. Wheeler :
> It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the whole
> thing totally bizarre. Is it me?
>
*shrug* The IS [NOT] NULL tests mean something different when applied
to a ROW than they do when applied to a scalar value or an array.
"SELECT 1 IS NULL"
David E. Wheeler wrote:
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
I guess the spec authors figured they might as well make IS [NOT] NULL
do something useful when applied to a row rather than throwing an
error. I tend to agree.
Frankly, I find the state where a record with a NULL and
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
Well, a ROW is an ordered set of values, each one of which may be
either NULL or NOT NULL.
Right.
It doesn't really make sense to talk about the ROW itself being NULL
or NOT NULL, only its member values (but for extra confusion, contrast
with
2009/7/24 David E. Wheeler :
> ROW(1, NULL) is neither NULL nor NOT NULL. I've no idea what state it is,
> but I guess that's the standard.
Well, a ROW is an ordered set of values, each one of which may be
either NULL or NOT NULL.
It doesn't really make sense to talk about the ROW itself being NU
On Jul 23, 2009, at 6:52 PM, David E. Wheeler wrote:
No, that's not the problem I see -- that solved the problem in my
particular code. The problem I see is that, given that the standard
says (according to Tom) that if any value is NULL then the record is
NULL, then I would expect this to r
On Jul 23, 2009, at 6:06 PM, Jeff Davis wrote:
However, in step 2, you transformed:
x IS NOT NULL => NOT x IS NULL
But in SQL that is not a tautology!
No, that's not the problem I see -- that solved the problem in my
particular code. The problem I see is that, given that the standard
say
On Jul 23, 2009, at 6:21 PM, Greg Stark wrote:
Are they not both null?
Isn't that just what you were complaining about not being the case:
Yes, but given that the standard says that `ROW(1, NULL)` is NULL,
then I would expect it to be NOT DISTINCT from `ROW(2, NULL)`.
Best,
David
--
Se
On Fri, Jul 24, 2009 at 1:32 AM, David E. Wheeler wrote:
> On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
>
>> Dunno how you can possibly come to that conclusion. Those row
>> values are certainly distinct (according to both PG and the spec).
>
> Are they not both null?
Isn't that just what you wer
On Thu, 2009-07-23 at 17:32 -0700, David E. Wheeler wrote:
> On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
>
> > Dunno how you can possibly come to that conclusion. Those row
> > values are certainly distinct (according to both PG and the spec).
>
> Are they not both null?
[ Is that a play on wo
On Jul 23, 2009, at 3:33 PM, Jeff Davis wrote:
not ROW(1, NULL) is null and not ROW(1, NULL) is not null
I don't know what the SQL standard says about this
Thanks Jeff, that's just what I needed.
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make chan
On Jul 23, 2009, at 4:22 PM, Tom Lane wrote:
Dunno how you can possibly come to that conclusion. Those row
values are certainly distinct (according to both PG and the spec).
Are they not both null?
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes t
"David E. Wheeler" writes:
> And yet they're DISTINCT FROM each other if either or both contain
> NULLs and some other values?
Well, that would depend on what the values were and in what columns...
> It seems to me that, to be consistent, it
> should be:
> select ROW(1, NULL) IS DISTIN
On Jul 23, 2009, at 4:08 PM, Tom Lane wrote:
This is per SQL standard. IS NULL is true if *all* the record's
fields are null; IS NOT NULL is true if *none* of them are.
Yeah, it's a bit dubious, but that's what they said.
And yet they're DISTINCT FROM each other if either or both contain
N
"David E. Wheeler" writes:
> How can a record be neither NULL or NOT NULL?
This is per SQL standard. IS NULL is true if *all* the record's
fields are null; IS NOT NULL is true if *none* of them are.
Yeah, it's a bit dubious, but that's what they said.
regards, tom lane
On Thu, 2009-07-23 at 15:19 -0700, David E. Wheeler wrote:
> How can a record be neither NULL or NOT NULL?
You could do:
not ROW(1, NULL) is null and not ROW(1, NULL) is not null
I don't know what the SQL standard says about this.
Regards,
Jeff Davis
--
Sent via pgsql-hackers mailing
On Jul 23, 2009, at 3:19 PM, David E. Wheeler wrote:
How can a record be neither NULL or NOT NULL?
try=# select ROW(1, NULL) IS NULL;
?column?
--
f
(1 row)
try=# select ROW(1, NULL) IS NOT NULL;
?column?
--
f
(1 row)
This makes it rather hard
How can a record be neither NULL or NOT NULL?
try=# select ROW(1, NULL) IS NULL;
?column?
--
f
(1 row)
try=# select ROW(1, NULL) IS NOT NULL;
?column?
--
f
(1 row)
This makes it rather hard to tell, in PL/pgSQL, when I've fetched the
56 matches
Mail list logo
