PostgreSQL Error: My function returns Empty

Question

EDIT!

I modified my function to this one

create or replace function get_by_distance(userlocation point)
returns table (name text, category text, subcategory text, department text, geolocation point, distance float)
as $BODY$
begin
return query
select listings."name", listings."category", listings."subcategory", listings."department", listings."geoloctaion", (userlocation <@> geoloctaion) as distance
from listings
where distance < 20
order by (userlocation <@> geoloctaion);
end;
$BODY$ language plpgsql;

And now I'm getting an empty return, it compiles and I can call it but I'm getting nothing from the editor. And I'm also trying to call it using an external api and I'm getting this error: Could not find the public.functionName() function in the schema cache But I don't get that error with other simple functions.

Summarizing: The function returns empty inside editor (while the actual select with same data returns the rows). The function returns an error when trying to call it from outside editor.

Answer 1

It looks like the line

(userlocation <@> geoloctaion)::point as distance

Is causing the problem.
The documentation says that the function returns a float

point <@> point ? float8

We could avoid the error by removing the cast ::point

(userlocation <@> geolocation as distance

Answer 2

I modified the function and now it returns nothing (empty). I can compile it and I can call it but it returns empty...

When I run the select only it returns me the Rows so there's a problem with the function itself.

Here's the snippet of the new try:

create or replace function get_by_distance(userlocation point)
returns table (name text, category text, subcategory text, department text, geolocation point, distance float)
as $BODY$
begin
return query
select listings."name", listings."category", listings."subcategory", listings."department", listings."geoloctaion", (userlocation <@> geoloctaion) as distance
from listings
where distance < 20
order by (userlocation <@> geoloctaion);
end;
$BODY$ language plpgsql;