Re: Calculating Minkowski distance between two rows

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