[GENERAL] Ref cursor with Geometric Function

[sunithab]

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.



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match