Re: Help with CREATE FUNCTION
От | David G. Johnston |
---|---|
Тема | Re: Help with CREATE FUNCTION |
Дата | |
Msg-id | CAKFQuwZ2YEvw7RQ_5exWm30E+dEXWrnQTAhrSokRSnu9HnyKxQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Help with CREATE FUNCTION (Kip Warner <kip@thevertigo.com>) |
Ответы |
Re: Help with CREATE FUNCTION
(Kip Warner <kip@thevertigo.com>)
|
Список | pgsql-novice |
I actually didn't realize what you meant. I didn't know SQL didn'tOn 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"
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.
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.
В списке pgsql-novice по дате отправления: