Re: Help with CREATE FUNCTION

On Mon, 2016-04-18 at 11:32 -0700, David G. Johnston wrote:
> 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);
> >
> >     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.
> >
> >
> 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"

I actually didn't realize what you meant. I didn't know SQL didn't
imply plpgsql here.

> Furthermore the constants you attempted to declare are not within the
> body of the function you are writing.

I tried already with the DECLARE, BEGIN, and END form already, but I'm
getting a syntax error on the END;

CREATE FUNCTION my_function(id_a integer, id_b integer) RETURNS float AS $$
DECLARE
    constant1 CONSTANT float := 0.123;
    constant2 CONSTANT float := 0.456;
    constant3 CONSTANT float := 0.789;
BEGIN
       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
END;
$$
LANGUAGE plpgsql;

SELECT my_function(1,2) AS similarity;

> I don't know how embedding them inside the function jives with:
>
> "...
> and they make more sense to be provided
>
> as part of the client side application's query.
> "

Because the client provides the function declaration as well.

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