<HTML dir=ltr><HEAD><TITLE>RE: [postgis-users] Problem with syntax in function</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.6000.16441" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText99722 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>I noticed that you have to build up the constant parameter sql because I think the bbox cast operators require a cstring. I'm not quite sure why unknown can be cast to cstring but text can't.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>I think you could still do the same thing using an SQL function if you use the GeomFromText function which accepts variable strings - downside you have to explode bbox to a polygon notation.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>So your function using SQL function would look something like (my order of exploding may be wrong)</FONT></DIV></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>CREATE OR REPLACE FUNCTION<BR>myschema.sel_by_pos2(varchar, varchar, varchar, varchar)<BR>RETURNS SETOF <FONT size=2>myschema.mytable </FONT>AS<BR>$BODY$</DIV>
<DIV dir=ltr> select * from <FONT size=2>myschema.mytable </FONT>where (<FONT size=2>geom_column</FONT><BR> && GeomFromText('POLYGON((' || $1 || ' ' || $2 || ', ' || $1 || ' ' || $4 || ', ' || $3 || ' ' || $4 || ', ' || $3 || ' ' || $4 || ', ' || $1 || ' ' || $2 || '))', <FONT size=2>26591</FONT>));</DIV>
<DIV dir=ltr>$BODY$ </DIV>
<DIV dir=ltr>LANGUAGE 'sql' VOLATILE;<BR></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>Thanks,</DIV>
<DIV dir=ltr>Regina</DIV>
<DIV dir=ltr><BR> </DIV>
<DIV dir=ltr>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr><FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Mark Cave-Ayland<BR><B>Sent:</B> Tue 5/22/2007 6:02 PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> RE: [postgis-users] Problem with syntax in function<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>On Tue, 2007-05-22 at 23:20 +0200, Ludovico Bianchini wrote:<BR>> I'm using PostgreSQL 8.1. Trying this<BR>><BR>> CREATE OR REPLACE FUNCTION<BR>> myschema.sel_by_pos(varchar, varchar, varchar,<BR>> varchar)<BR>> RETURNS SETOF myschema.mytable AS<BR>> $BODY$<BR>> select * from myschema.mytable where (geom_column<BR>> &&<BR>> SetSRID('BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||<BR>> ' ' || $4 || ')'::box3d, 26591))<BR>> $BODY$> LANGUAGE 'sql' VOLATILE;<BR>><BR>> I get: ERROR: BOX3D parser - doesnt start with BOX3D(<BR>> CONTEXT: SQL function "sel_by_pos"<BR>><BR>> I'm in very trouble with this behaviour...<BR><BR><BR>Hi Ludovico,<BR><BR>Because you are building up a parameter using string operators, you need<BR>to generate the SQL statement as a string and then execute it. So you'll<BR>need to use PL/PGSQL rather than just SQL, e.g. something like:<BR><BR><BR>CREATE OR REPLACE FUNCTION<BR>myschema.sel_by_pos(varchar, varchar, varchar, varchar)<BR>RETURNS SETOF myschema.mytable AS<BR>$$<BR>DECLARE<BR> str text;<BR> rec record;<BR>BEGIN<BR> str := 'select * from myschema.mytable where (geom_column<BR> && SetSRID(''BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||<BR> ' ' || $4 || ')''::box3d, 26591))';<BR> <BR> -- For debugging only<BR> raise notice 'str is: %', str;<BR><BR> for rec in execute str loop<BR> return next rec;<BR> end loop;<BR><BR>END<BR>$$ LANGUAGE 'plpgsql' VOLATILE;<BR><BR><BR>Kind regards,<BR><BR>Mark.<BR><BR>--<BR>ILande - Open Source Consultancy<BR><A href="http://www.ilande.co.uk/">http://www.ilande.co.uk</A><BR><BR><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>