Re: Calculating Minkowski distance between two rows

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@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
>
> --
> */Babak Alipour ,/*
> */University of Florida/*


--
Adrian Klaver
adrian.klaver@aklaver.com