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.
