Re: Help with CREATE FUNCTION

On Mon, 2016-04-18 at 08:00 -0700, David G. Johnston wrote:
> Arguably its still stupid :)
>
> SELECT [...]
> FROM (SELECT * FROM my_table WHERE id = arg_id_a) AS ta
> CROSS JOIN (SELECT * FROM my_table WHERE id = arg_id_b) AS tb
>
> David J.

Thank you very much David and Sándor. If I understand correctly, the
function should then look like so...

DROP FUNCTION IF EXISTS my_function(id_a integer, id_b integer);

constant1 CONSTANT float := 0.123;
constant2 CONSTANT float := 0.456;
constant3 CONSTANT float := 0.789;

CREATE FUNCTION my_function(id_a integer, id_b integer) RETURNS float AS $$
SELECT
(constant1 * ABS(ta.col1 - tb.col1)) +
(constant2 * ABS(ta.col2 - tb.col2)) +
(constant3 * ABS(ta.col3 - tb.col3))
FROM (SELECT * FROM my_table WHERE id = id_a) AS ta
CROSS JOIN (SELECT * FROM my_table WHERE id = id_b) AS tb
$$ LANGUAGE SQL;

SELECT my_function(1,2) AS similarity;

I've looked at the syntax for the constants and they are giving me a
syntax error. I also tried flanking them with a DECLARE, BEGIN, END,
but same problem.

--
Kip Warner -- Senior Software Engineer
OpenPGP encrypted/signed mail preferred
http://www.thevertigo.com