Re: Help with CREATE FUNCTION

On Mon, Apr 18, 2016 at 11:39 AM, Kip Warner <kip(at)thevertigo(dot)com> wrote:

> 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(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.
> > >
> > >
> > 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.
>
>
​
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Unlike with SQL language functions you must explicitly return values from
pl/pgsql function​. The documentation describes the various ways to
accomplish this.

David J.