From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | make view with union return one record |
Date: | 2010-07-06 19:43:12 |
Message-ID: | 4C338750.9090305@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
select * from (
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
) as x limit 1;
I dont think I can put the limit outside the view, because the "new
stuff" is going to select multiple parcels like:
select * from getPoint where pin in ('123', '456', '789);
I thought of changing it to a function, but its in use in many places in
the code as:
select * from getPoint where pin = '12345';
So I've run out of ideas now. Any hints or pointers on how I can get
the limit to work inside the view? Or some other way?
Thanks for your time,
-Andy
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2010-07-06 19:58:45 | Re: TupleDesc and HeapTuple |
Previous Message | Guy Rouillier | 2010-07-06 16:59:39 | Re: SQL Query Help Please ! |