[postgis-users] Problem with syntax in function

[Obe, Regina]

If you are using 8.0 and above - you can use $ quoting syntax.  $ quoting is much saner to read and write than trying to escape out quotes.  So your function would look something like 
 
CREATE OR REPLACE FUNCTION myschema.sel_by_pos(varchar, varchar, varchar,
varchar)
  RETURNS SETOF myschema.mytable AS
$BODY$
 select * from myschema.mytable where (geom_column &&
SetSRID('BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||
' ' || $4 || ')'::box3d, 26591))
 $BODY$
  LANGUAGE 'sql' 
LANGUAGE 'sql' VOLATILE;
 
I may have screwed up with the above so check my logic, but hopefully you get the idea.  
 

________________________________

From: postgis-users-bounces at postgis.refractions.net on behalf of Ludovico Bianchini
Sent: Tue 5/22/2007 10:21 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Problem with syntax in function



Hi,
I've a table with a geometry column (POINT).

I've this working query to extract data:
select * from myschema.mytable where (geom_column &&
SetSRID('BOX3D(1754642 5059039, 1754644
5059041)'::box3d, 26591))

I've tried to create a function from this query but I
don't understand the use of quote to concat the string
arguments. I've tried this

CREATE OR REPLACE FUNCTION
myschema.sel_by_pos(varchar, varchar, varchar,
varchar)
RETURNS SETOF myschema.mytable AS
'select * from myschema.mytable where (geom_column &&
SetSRID(''BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||
' ' || $4 || ')''::box3d, 26591))'
LANGUAGE 'sql' VOLATILE;

and I get ERROR:  syntax error at or near "||" at
character 223

I don't understand where is the problem...


       

       
               
___________________________________
L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail:
http://it.docs.yahoo.com/nowyoucan.html
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users




-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070522/af270616/attachment.html>