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

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