Re: make view with union return one record

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