From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: make view with union return one record |
Date: | 2010-07-07 14:27:32 |
Message-ID: | 4C348ED4.7080204@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7/7/2010 8:27 AM, Merlin Moncure wrote:
> On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson<andy(at)squeakycode(dot)net> wrote:
>> I have gis data in layers, and a pin might appear in either layer, or both
>> (parcelPoly, parcelPoint), or neither (and I dont care which I find)... so I
>> have this view:
>>
>>
>> create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
>> SELECT gid,
>> st_x(st_centroid(the_geom)) AS x,
>> st_y(st_centroid(the_geom)) AS y,
>> acreage,
>> county_pin,
>> st_box2d(st_expand(the_geom, 100))
>> FROM howardia.parcelPoly
>> UNION ALL
>> SELECT gid,
>> st_x(the_geom) AS x,
>> st_y(the_geom) AS y,
>> acreage,
>> county_pin,
>> st_box2d(st_expand(the_geom, 100))
>> FROM howardia.parcelPoint;
>>
>> Which works fine for what I'm using.. it returns one or two records, and my
>> code just takes the first record and runs with it.
>>
>> but now... I'm adding something new, and having it return multiple records
>> per pin is causing problems. I tried adding a limit inside the view but
>> then it never returns anything:
>
> ??? why not -- can you double check that?
>
> create view l as select * from (select 'a' union all select 'b') q limit 1;
>
> select * from l;
> ?column?
> ----------
> a
> (1 row)
>
> merlin
Ok, I found it. I don't know why I was making it more difficult than it
needed to be. Once I stopped thinking about the view, and thought of it
just like any other table, then I have dups, and I want one of each..
and that can be solved with distinct on:
select distinct on (pin) *
from getpoint
where pin in ('110250821020000', '320770000010000');
Thanks for letting me think out loud.
-Andy
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-07-07 15:54:15 | Re: Trigger for modification timestamp column |
Previous Message | Tom Lane | 2010-07-07 14:20:05 | Re: optimizer choosing the wrong index |