[postgis-users] Problem with syntax in function

[Mark Cave-Ayland]

On Tue, 2007-05-22 at 23:20 +0200, Ludovico Bianchini wrote:
> I'm using PostgreSQL 8.1. Trying this 
> 
> 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' VOLATILE;
> 
> I get: ERROR:  BOX3D parser - doesnt start with BOX3D(
> CONTEXT:  SQL function "sel_by_pos"
> 
> I'm in very trouble with this behaviour...


Hi Ludovico,

Because you are building up a parameter using string operators, you need
to generate the SQL statement as a string and then execute it. So you'll
need to use PL/PGSQL rather than just SQL, e.g. something like:


CREATE OR REPLACE FUNCTION
myschema.sel_by_pos(varchar, varchar, varchar, varchar)
RETURNS SETOF myschema.mytable AS
$$
DECLARE
	str text;
    rec record;
BEGIN
	str := 'select * from myschema.mytable where (geom_column
 && SetSRID(''BOX3D(' || $1 || ' ' || $2 || ', ' || $3 ||
 ' ' || $4 || ')''::box3d, 26591))';
	
    -- For debugging only
	raise notice 'str is: %', str;

    for rec in execute str loop
        return next rec;
    end loop;

END
$$ LANGUAGE 'plpgsql' VOLATILE;


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk