PostgreSQL Error: My function returns Empty-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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;