From: | <sunithab(at)travelpost(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Ref cursor with Geometric Function |
Date: | 2006-01-16 20:17:26 |
Message-ID: | 000301c61ad9$de983420$1301a8c0@sf.vagabond.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
The following query is working when this is used by static cursor. This will
return all the properties within the distance of 20 miles
v_point is a local variable. 'POINT(' || v_rec.lon || ' ' || v_rec.lat ||
')'
SELECT property_rating_id
, property_id
, property_name_fixed
FROM property.vw_property_rating pr
INNER JOIN property.property_map2ns pm ON(pr.property_id = pm.tp_id)
WHERE DISTANCE( v_point :: geometry,
POINTFromText('Point(' ||pm.long ||' ' ||
pm.lat||')')::geometry) < .4;
But When I am trying to build the dynamic sql string to a refcursor it is
not able to identify the syntax
Below is the dynamic sql string not working
v_sqladd = ' DISTANCE( v_point :: geometry,
POINTFromText(' || ' Point(' ||pm.long ||' ' ||
pm.lat||')'||')::geometry) < .4';
sqlstr = 'SELECT property_rating_id
, property_id
, property_name_fixed
FROM property.vw_property_rating pr
INNER JOIN property.property_map2ns pm ON(pr.property_id = pm.tp_id)
WHERE '|| p_condition || ' and ' || v_sqlAdd ;
I am trying add the v_sqladd to sqlstr and not working. The error is pm.
Can anybody help me.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-01-16 21:20:23 | Re: Ref cursor with Geometric Function |
Previous Message | Matthew Schumacher | 2006-01-16 19:57:34 | Re: Rule system and unsucessful updates. |