Tom Lane wrote:
Shilong Stanley Yao <[EMAIL PROTECTED]> writes:Thank you very much for this nice solution. It worked very well!
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
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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
