Re: Help with CREATE FUNCTION

On Monday, April 18, 2016, Kip Warner <kip(at)thevertigo(dot)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);
>
> 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.
>
>
Begin/end etc only work for pl/pgsql functions.

What version of PostgreSQL?

David J.