On Mon, Apr 18, 2016 at 10:26 AM, Kip Warner <kip@thevertigo.com> wrote:
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);
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.
DROP FUNCTION -------;
CREATE FUNCTION ------;
AS $$
DECLARE
constant1 CONSTANT float := 0.123;
[...]
BEGIN
DO STUFF HERE
END;
$$
LANGUAGE plpgsql
As I said you wrote the function in "SQL" language an not "plpgsql"
Furthermore the constants you attempted to declare are not within the body of the function you are writing.
I don't know how embedding them inside the function jives with: