I have a user-defined function to calculate Euclidean distance. Please tell me how to use SQL to output the following expected results.
■ cosine_similarity function
postgres=# SELECT public.cosine_similarity(ARRAY[ 0.1, 0.2, 0.3 ],ARRAY[ 0.1, 0.2, 0.3 ]);
cosine_similarity
-------------------
1
(1 row)
postgres=# SELECT public.cosine_similarity(ARRAY[ 0.4, 0.5, 0.6 ],ARRAY[ 0.1, 0.2, 0.3 ]);
cosine_similarity
--------------------
0.9746318461970762
(1 row)
■ image table
postgres=# select feature from image order by id;
feature
---------------
{0.1,0.2,0.3}
{0.4,0.5,0.6}
(2 rows)
■ SQL and output
postgres=# SELECT public.cosine_similarity((select feature from image order by id), ARRAY[ 0.1,0.2,0.3]);
ERROR: more than one row returned by a subquery used as an expression
■ Expectation
cosine_similarity
--------------------
1
0.9746318461970762
(2 row)
■ Function
postgres=# SELECT
routine_definition
FROM
information_schema.routines
WHERE
specific_schema LIKE 'public'
AND routine_name LIKE 'cosine_similarity';
routine_definition
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
BEGIN +
RETURN (select ((select public.dot_product(vector1, vector2) as dot_pod)/((select public.vector_norm(vector1) as norm1) * (select public.vector_norm(vector2) as norm2))) AS similarity_value);+
END;
■ Reference
How to get cosine distance between two vectors in postgres? https://stackoverflow.com/a/58095682/1582304
Update 1
■ Updated SQL
I think I found an answer. It looks good in SQL below.
postgres=# select public.cosine_similarity(feature, ARRAY[ 0.1,0.2,0.3]) from image order by id;
cosine_similarity
--------------------
1
0.9746318461970762
