From: | Babak Alipour <babak(dot)alipour(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Calculating Minkowski distance between two rows |
Date: | 2016-04-25 14:07:14 |
Message-ID: | CA+_of16FyVzbvfeoBohRsZSDxurHDGY-2t1zThTfV4_446bz3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
Best regards,
Babak Alipour
--
*Babak Alipour ,*
*University of Florida*
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2016-04-25 14:20:11 | Re: Columnar store as default for PostgreSQL 10? |
Previous Message | Achilleas Mantzios | 2016-04-25 14:04:54 | Re: psql color hostname prompt |