ERROR: function does not exist when invoking the function using spring-data repository method

49 Views Asked by At

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)
0

There are 0 best solutions below