Thread: Character escape in "CREATE FUNCTION ..."

Dear All,
I am trying to write a function in Postgresql, which takes 2 floats and
returns a box.  But seems the nested single-quote in the AS clause
prevent $1 and $2 from being expanded.  Besides writing a C function
instead of a SQL one, is there any way to solve this problem?

Thanks a lot.
-Stan

CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box
         AS 'SELECT box \'(($1, $2), (1.3, 1.4))\''
         LANGUAGE 'sql'
         WITH (ISCACHABLE);


Error message:
psql:func_radec_to_box.sql:4: ERROR:  Bad box external representation
'(($1, $2), (1.3, 1.4))'

On Monday 15 March 2004 18:38, Shilong Stanley Yao wrote:
> Dear All,
> I am trying to write a function in Postgresql, which takes 2 floats and
> returns a box.  But seems the nested single-quote in the AS clause
> prevent $1 and $2 from being expanded.  Besides writing a C function
> instead of a SQL one, is there any way to solve this problem?

> CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box
>          AS 'SELECT box \'(($1, $2), (1.3, 1.4))\''
>          LANGUAGE 'sql'
>          WITH (ISCACHABLE);

If the box constructor is supposed to take a string, try something like:

SELECT box \'((\' || $1 || \'...etc

That is to say - use string concatenation

--
  Richard Huxton
  Archonet Ltd

On Mon, Mar 15, 2004 at 11:38:05 -0700,
  Shilong Stanley Yao <yao@noao.edu> wrote:
> Dear All,
> I am trying to write a function in Postgresql, which takes 2 floats and
> returns a box.  But seems the nested single-quote in the AS clause
> prevent $1 and $2 from being expanded.  Besides writing a C function
> instead of a SQL one, is there any way to solve this problem?

I believe you want to concatenate $1 and $2 with the constants parts
of the string rather than to embed them in the string.

The cube type in contrib/cube already has functions for making a cube
from float8 numbers without converting to text inbetween. Depending
on what you want this might be another approach for you.

>
> Thanks a lot.
> -Stan
>
> CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box
>         AS 'SELECT box \'(($1, $2), (1.3, 1.4))\''
>         LANGUAGE 'sql'
>         WITH (ISCACHABLE);
>
>
> Error message:
> psql:func_radec_to_box.sql:4: ERROR:  Bad box external representation
> '(($1, $2), (1.3, 1.4))'
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html

Shilong Stanley Yao <yao@noao.edu> writes:
> I am trying to write a function in Postgresql, which takes 2 floats and
> returns a box.  But seems the nested single-quote in the AS clause
> prevent $1 and $2 from being expanded.  Besides writing a C function
> instead of a SQL one, is there any way to solve this problem?

> CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box
>          AS 'SELECT box \'(($1, $2), (1.3, 1.4))\''
>          LANGUAGE 'sql'
>          WITH (ISCACHABLE);

This is never going to work because you are trying to use the
typed-literal syntax with something that you don't actually want to
be a literal constant.  You need to think in terms of a function, not
a literal.  In this case I think what you want is the box-from-two-points
constructor function, together with the point-from-two-floats constructor:

    ... AS 'SELECT box(point($1, $2), point(1.3, 1.4))'

If you had a mind to, you could write the constant point as a literal:

    ... AS 'SELECT box(point($1, $2), point \'1.3, 1.4\')'

but you can't write the variable point as a literal.

            regards, tom lane

Tom Lane wrote:
> Shilong Stanley Yao <yao@noao.edu> writes:
>
>>I am trying to write a function in Postgresql, which takes 2 floats and
>>returns a box.  But seems the nested single-quote in the AS clause
>>prevent $1 and $2 from being expanded.  Besides writing a C function
>>instead of a SQL one, is there any way to solve this problem?
>
>
>>CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box
>>         AS 'SELECT box \'(($1, $2), (1.3, 1.4))\''
>>         LANGUAGE 'sql'
>>         WITH (ISCACHABLE);
>
>
> This is never going to work because you are trying to use the
> typed-literal syntax with something that you don't actually want to
> be a literal constant.  You need to think in terms of a function, not
> a literal.  In this case I think what you want is the box-from-two-points
> constructor function, together with the point-from-two-floats constructor:
>
>     ... AS 'SELECT box(point($1, $2), point(1.3, 1.4))'
>
> If you had a mind to, you could write the constant point as a literal:
>
>     ... AS 'SELECT box(point($1, $2), point \'1.3, 1.4\')'
>
> but you can't write the variable point as a literal.
>
>             regards, tom lane
Thank you very much for this nice solution.  It worked very well!
BTW, a spatial query involving RTREE indexes showes that SQL function is
  much slower than C function, which is within the expectation.
Thanks everyone of the previous responses for your help too!
Stan