Ref cursor with Geometric Function

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.

Responses

Browse pgsql-general by date

  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.