<HTML dir=ltr><HEAD><TITLE>[postgis-users] Problem with syntax in function</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3086" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText37021 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>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 </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr>CREATE OR REPLACE FUNCTION <FONT size=2>myschema.sel_by_pos(varchar, varchar, varchar,<BR>varchar)</FONT><BR> RETURNS <FONT size=2>SETOF myschema.mytable AS</FONT><BR>$BODY$<BR> <FONT size=2>select * from myschema.mytable where (geom_column &&<BR>SetSRID('BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||<BR>' ' || $4 || ')'::box3d, 26591))</FONT><BR> $BODY$<BR> LANGUAGE 'sql' </DIV>
<DIV dir=ltr><FONT size=2>LANGUAGE 'sql' VOLATILE;</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>I may have screwed up with the above so check my logic, but hopefully you get the idea. </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Ludovico Bianchini<BR><B>Sent:</B> Tue 5/22/2007 10:21 AM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Problem with syntax in function<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Hi,<BR>I've a table with a geometry column (POINT).<BR><BR>I've this working query to extract data:<BR>select * from myschema.mytable where (geom_column &&<BR>SetSRID('BOX3D(1754642 5059039, 1754644<BR>5059041)'::box3d, 26591))<BR><BR>I've tried to create a function from this query but I<BR>don't understand the use of quote to concat the string<BR>arguments. I've tried this<BR><BR>CREATE OR REPLACE FUNCTION<BR>myschema.sel_by_pos(varchar, varchar, varchar,<BR>varchar)<BR>RETURNS SETOF myschema.mytable AS<BR>'select * from myschema.mytable where (geom_column &&<BR>SetSRID(''BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||<BR>' ' || $4 || ')''::box3d, 26591))'<BR>LANGUAGE 'sql' VOLATILE;<BR><BR>and I get ERROR: syntax error at or near "||" at<BR>character 223<BR><BR>I don't understand where is the problem...<BR><BR><BR> <BR><BR> <BR> <BR>___________________________________<BR>L'email della prossima generazione? Puoi averla con la nuova Yahoo! Mail:<BR><A href="http://it.docs.yahoo.com/nowyoucan.html">http://it.docs.yahoo.com/nowyoucan.html</A><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>