[Fwd: Re: [GENERAL] Crosstab Problems]

Oops, just noticed I sent this to the General list instead of Patches --
sorry about that.

Joe

-------- Original Message --------
Subject: Re: [GENERAL] Crosstab Problems
Date: Wed, 24 Oct 2007 19:26:16 -0700
From: Joe Conway <mail(at)joeconway(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
CC: Jorge Godoy <jgodoy(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org,
Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>
References: <4E6E765B-2899-4B85-9131-A8847FB06305(at)grid(dot)unep(dot)ch>
<24199(dot)1192722457(at)sss(dot)pgh(dot)pa(dot)us> <4717A807(dot)1030209(at)joeconway(dot)com>
<200710182102(dot)56340(dot)jgodoy(at)gmail(dot)com> <3161(dot)1192763352(at)sss(dot)pgh(dot)pa(dot)us>

Tom Lane wrote:
> Jorge Godoy <jgodoy(at)gmail(dot)com> writes:
>> Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu:
>>> The row is pretty useless without a rowid in this context -- it seems
>>> like the best thing to do would be to skip those rows entirely. Of
>>> course you could argue I suppose that it ought to throw an ERROR and
>>> bail out entirely. Maybe a good compromise would be to skip the row but
>>> throw a NOTICE?
>
>> If I were using it and having this problem I'd rather have an ERROR.
>
> I can think of four reasonably credible alternatives:
>
> 1. Treat NULL rowid as a category in its own right. This would conform
> with the behavior of GROUP BY and DISTINCT, for instance.

> 4. Silently ignore rows with NULL rowid.

After looking closer I realized that #4 was my original intention, and
there was even code attempting to implement it, but just not very well ;-(.

In any case, the attached changes the behavior to #1 for both flavors of
crosstab (the original crosstab(text, int) and the usually more useful
crosstab(text, text)).

It is appropriate for 8.3 but not back-patching as it changes behavior
in a non-backward compatible way and is probably too invasive anyway.
I'll do something much simpler just to prevent crashing for 8.2 and earlier.

If there are no objections I'll apply Thursday.

Joe