Custom sql function find_accounts_within_radius() fails when invoked via spring data repository method with an error:
ERROR: function find_accounts_within_radius(geometry, double precision, integer) does not exist
No function matches the given name and argument types. You might need to add explicit type casts
the function is defined as follows:
CREATE OR REPLACE FUNCTION find_accounts_within_radius(reference_point geometry, radius numeric, lim integer default 2)
RETURNS TABLE (id bigint, username varchar, distance double precision) AS $$
BEGIN
RETURN QUERY
SELECT acc.id, acc.username, acc.geolocation <-> reference_point as distance
FROM accounts acc
WHERE st_dwithin(reference_point, acc.geolocation, radius)
order by distance, id
limit lim;
END $$ LANGUAGE plpgsql;
The repository method is defined as a native query:
@Query(value = "select find_accounts_within_radius(:reference_point, :radius, :lim)", nativeQuery = true)
fun findAccounts(@Param("reference_point") point: Point, @Param("radius") radius: Double, @Param("lim") limit: Int = 2): List<AccountSummary>
where the Point is of type: org.locationtech.jts.geom.Point.
Invoking the same function directly in the database works as expected:
select find_accounts_within_radius(st_makepoint(16.4321, 43.4321), 10000, 2)