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