Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ref cursor with Geometric Function

Ref cursor with Geometric Function

From
Date:
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.

Re: Ref cursor with Geometric Function

From
Michael Fuhr
Date:
On Mon, Jan 16, 2006 at 12:17:26PM -0800, sunithab@travelpost.com wrote:
> The following query is working when this is used by static cursor. This
> will return all the properties within the distance of 20 miles
[...]
> But When I am trying to build the dynamic sql string to a refcursor it is
> not able to identify the syntax

Could you post a complete function instead of just excerpts?  That
would make it easier to understand what you're doing and to see
where the problem is.  Please simplify the example function as much
as possible -- it should have enough code to demonstrate the problem
and to work in some sense if the problem didn't happen, but no more
code than that (some people post long, complex functions or queries
when the real problem could be demonstrated in only a few lines;
the extraneous code isn't relevant to the problem and just gets in
the way of debugging).

--
Michael Fuhr

Re: Ref cursor with Geometric Function

From
"Jim C. Nasby"
Date:
On Mon, Jan 16, 2006 at 02:20:23PM -0700, Michael Fuhr wrote:
> On Mon, Jan 16, 2006 at 12:17:26PM -0800, sunithab@travelpost.com wrote:
> > The following query is working when this is used by static cursor. This
> > will return all the properties within the distance of 20 miles
> [...]
> > But When I am trying to build the dynamic sql string to a refcursor it is
> > not able to identify the syntax
>
> Could you post a complete function instead of just excerpts?  That

Complete error message would be helpful too...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461