Re: row literal problem

On Wed, Jul 18, 2012 at 3:42 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 07/18/2012 03:30 PM, Tom Lane wrote:
>>
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>>
>>> On 07/18/2012 03:18 PM, Merlin Moncure wrote:
>>>>
>>>> there are no null fields, right? if the last field is sometimes null
>>>> you'd see that (you probably ruled that out though). when you say
>>>> 'sometimes', do you mean for some rows and not others? or for some
>>> queries?
>>>
>>> No, the inner query has two fields.
>>> It happens for all rows, but not for all two-field-resulting queries as
>>> q. I'm trying to find a simple case rather than the rather complex query
>>> my customer is using.
>>
>> I'm wondering about a rowtype with a third, dropped column.
>
>
>
> As usual Tom has hit the nail on the head. Here's a simple test case that
> demonstrates the problem. I could probably have cut it down more but I was
> following the structure of the original somewhat:
>
> # with q as
> (
> select max(nspname) as nspname, sum(allind.count) as indices
> from (select indrelid, count(*)
> from pg_index
> group by indrelid) allind
> left outer join pg_class on pg_class.oid = allind.indrelid
> left outer join pg_namespace on pg_class.relnamespace =
> pg_namespace.oid
> group by pg_namespace.oid
> )
> select q from q;
>
> q
> --------------------
> (pg_catalog,91,11)
> (pg_toast,18,99)
> (2 rows)

hm, it's the 'group by' -- for example if you add group by
pg_namespace.oid, group by pg_namespace.oid || 'abc', you can invent
columns that come back into the rowtype.

merlin