Search Postgresql Archives

Re: Calculating Minkowski distance between two rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 04/25/2016 07:26 AM, Babak Alipour wrote:
That is correct. The function I've written only works when the two
tables are named table_train and table_test; is it possible to
generalize that to take in any two tables?

I'm heading out the door and off the top of my head:

1) Get tables names as text.

2) Get the row conditions as text.

3) Use EXECUTE to build a query string:
http://www.postgresql.org/docs/9.5/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

In particular:
"A cleaner approach is to use format()'s %I specification for table or column names (strings separated by a newline are concatenated):"

http://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-FORMAT

4) SELECT the result INTO a record variable:

http://www.postgresql.org/docs/9.5/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS

5) Do your calculations

6) Not considered, validating that number of table columns are the same and the types are compatible.


Thanks in advance.

Babak

On Mon, Apr 25, 2016 at 10:24 AM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:

    On 04/25/2016 07:07 AM, Babak Alipour wrote:

        Greetings everyone,

        I'm a novice plpgsql user.
        For an application, I'm trying to write a user-defined function that
        takes a row of some table (let's say with k fields) and takes
        another
        row from another table (again with k fields); then calculate the
        Euclidean, Manhattan or generally Minkowski distance (with some
        p) and
        then return an integer.
        I've written this:

        CREATE FUNCTION euclidean_distance(row1 table_train, row2
        table_test,
        OUT distance DOUBLE PRECISION) AS $$
        DECLARE
        tmp DOUBLE PRECISION;
        BEGIN
        FOR col IN SELECT column_name FROM information_schema.columns WHERE
        table_name=table_train LOOP
           tmp := (row1.col - row2.col);
           distance += tmp*tmp;
        END LOOP;
        distance := sqrt(distance);
        END;
        $$ LANGUAGE plpgsql;

        Could anyone please help me fix this function so that I can pass
        any two
        rows of two tables (with same number of columns) and have their
        distance
        returned.


    You are already doing that, so do you mean any two rows of any two
    tables?


        Best regards,
        Babak Alipour

        --
        */Babak Alipour ,/*
        */University of Florida/*



    --
    Adrian Klaver
    adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>




--
*/Babak Alipour ,/*
*/University of Florida/*


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux