Обсуждение: Help with CREATE FUNCTION

Hey list,

I am having difficulty using CREATE FUNCTION with a subordinate SELECT
query that should operate on exactly two rows.

    http://pastebin.com/7b3Vxbuh

Any help is appreciated.

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

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

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.

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;

On Mon, 2016-04-18 at 10:57 -0700, David G. Johnston wrote:
> Begin/end etc only work for pl/pgsql functions.
>
> What version of PostgreSQL?

I'm using 9.4.

> What are these supposed to represent?

They are pre-calculated constants that are part of a more complex
algorithm. I can't submit them when I install the database schema
because they may change later and they make more sense to be provided
as part of the client side application's query.

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

On Mon, 2016-04-18 at 10:57 -0700, David G. Johnston wrote:
> Begin/end etc only work for pl/pgsql functions.
>
> What version of PostgreSQL?

I'm using 9.4.

> What are these supposed to represent?

They are pre-calculated constants that are part of a more complex
algorithm. I can't submit them when I install the database schema
because they may change later

 and they make more sense to be provided

as part of the client side application's query. 

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Monday, April 18, 2016, Kip Warner <kip@thevertigo.com> wrote:
>> They are pre-calculated constants that are part of a more complex
>> algorithm. I can't submit them when I install the database schema
>> because they may change later

> There is no provision in PostgreSQL for constructing a global variable in
> that fashion.  You could maybe leverage GUCs to that effect, though.

If they might change later, they're not really constants, hm?  The
usual advice for this sort of thing is to store them in a table.

            regards, tom lane

On Mon, 2016-04-18 at 14:19 -0400, Tom Lane wrote:
> If they might change later, they're not really constants, hm?  The
> usual advice for this sort of thing is to store them in a table.

They're variable only in the sense that they could vary after many
months, but constant enough that treating them as such allows us the
potential benefit of optimization.

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

On Mon, 2016-04-18 at 11:17 -0700, David G. Johnston wrote:
> Then add three more arguments to the function and refer to those
> arguments like you do the two ids.

I would rather not because there's about 32 of these constants and
conceptually it doesn't make sense to provide them as arguments when
they are in practise immutable.

> There is no provision in PostgreSQL for constructing a global
> variable in that fashion.  You could maybe leverage GUCs to that
> effect, though.

I guess I am misunderstanding the variable declaration syntax?

http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html

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

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

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

Hey David. I think what I'll do is not define the constants using the
CONSTANT syntax, but instead have them inline within the SELECT as I
had before. Since the query is never seen by a human, there's really no
reason to have to beautify it.

On a related note, if I SELECT my_function(123,4) and one of those row
IDs doesn't exist in my_table, it should probably error which it
doesn't do right now. What would be the most elegant way of handling
that scenario?

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

On Mon, 2016-04-18 at 17:32 -0700, David G. Johnston wrote:
> If you are sticking with pl/pgsql then the most direct solution is to
> simply:

Hey David.

> DECLARE
> function_variable type;
> BEGIN
>
> SELECT [result]
> FROM ... CROSS JOIN ...
> INTO STRICT function_variable;
>
> RETURN function_variable;
> END;
>
> The STRICT will enforce that exactly one row is returned by the
> function.
>
> A more user-friendly way would be to *also* do:
>
> PERFORM * FROM tbl WHERE id = a_id;
> IF NOT FOUND THEN
> RAISE EXCEPTION 'Must supply a known a_id'
> END IF;
>
> and repeat for the other id.
>
> I say *also* because it is still a good idea to ensure that when you
> are
> only expecting a single result row that you are getting a single
> result
> row.  The STRICT acts like an assertion in that sense - meant for
> debugging
> but should never been seen by an end-user unless something is
> seriously
> wrong.
>
> If you are going to try and leverage SQL for this now - since you no
> longer
> need variables - your options are limited, possibly non-existent
> within the
> function itself.  Any useful solution is probably worse than just
> using
> pl/pgsql.  You can force SQL to choke if you see more than one row
> when
> only one should be present but it has no qualms seeing an empty set
> in
> those same circumstances.

I reckon by now you've successfully convinced me of the merits of
 plpgsql over vanilla ANSI sql for this. =) Your solution is very
elegant and functional. Thank you.

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