Unexpected behaviour of postgres function .

[linux-devil]

psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) post grest-v6.0.2

I just wrote a new function after adding another table into my schema

CREATE OR REPLACE FUNCTION public.find_closest_new(lat double precision,lon double precision) RETURNS table(device_id integer, latitude double precision, longitude double precision,name character varying(128)) as $$ SELECT deviceid, latitude, longitude,guard_info.name FROM tc_positions INNER JOIN guard_info on tc_positions.deviceid = guard_info.guard_id WHERE ST_Distance_Sphere(ST_MakePoint(tc_positions.longitude, tc_positions.latitude),ST_MakePoint(lon,lat)) <= 2 * 1609.34 and tc_positions.devicetime >= timestamp '2020-01-30T13:56:02' and tc_positions.devicetime < timestamp '2020-01-30T15:56:02'; $$ LANGUAGE SQL;

Now upon calling the function using

curl -X POST \ http://localhost:3000/rpc/find_closest_new \ -H 'Content-Type: application/json' \ -H 'Prefer: params=single-object' \ -d '{"type":"featurecollection","features":{"lat":12.976178,"lon":77.6434156}}'

I get following error : 127.0.0.1 - - [12/Apr/2020:14:44:28 +0000] "POST /rpc/find_closest_new HTTP/1.1" 404 - "" "curl/7.58.0"

{"hint":"No function matches the given name and argument types. You might need to add explicit type casts.","details":null,"code":"42883","message":"function public.find_closest_new(json) does not exist"}

I am not sure what am I doing here , while I successfully wrote another function without join which is running fine. here it is : CREATE OR REPLACE FUNCTION public.find_closest(lat double precision,lon double precision) RETURNS table(device_id integer, latitude double precision, longitude double precision) as $$ SELECT deviceid, latitude, longitude FROM tc_positions WHERE ST_Distance_Sphere(ST_MakePoint(tc_positions.longitude, tc_positions.latitude),ST_MakePoint(lon,lat)) <= 2 * 1609.34 and devicetime >= timestamp '2020-01-30T13:56:02' and devicetime < timestamp '2020-01-30T15:56:02'; $$ LANGUAGE SQL;

[ruslantalpa]

Restart postgrest after each schema change

On 12 Apr 2020, at 18:11, Harshit Sharma notifications@github.com wrote:

 psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) post grest-v6.0.2

I just wrote a new function after adding another table into my schema

CREATE OR REPLACE FUNCTION public.find_closest_new(lat double precision,lon double precision) RETURNS table(device_id integer, latitude double precision, longitude double precision,name character varying(128)) as $$ SELECT deviceid, latitude, longitude,guard_info.name FROM tc_positions INNER JOIN guard_info on tc_positions.deviceid = guard_info.guard_id WHERE ST_Distance_Sphere(ST_MakePoint(tc_positions.longitude, tc_positions.latitude),ST_MakePoint(lon,lat)) <= 2 * 1609.34 and tc_positions.devicetime >= timestamp '2020-01-30T13:56:02' and tc_positions.devicetime < timestamp '2020-01-30T15:56:02'; $$ LANGUAGE SQL;

Now upon calling the function using

curl -X POST \ http://localhost:3000/rpc/find_closest_new \ -H 'Content-Type: application/json' \ -H 'Prefer: params=single-object' \ -d '{"type":"featurecollection","features":{"lat":12.976178,"lon":77.6434156}}'

I get following error : 127.0.0.1 - - [12/Apr/2020:14:44:28 +0000] "POST /rpc/find_closest_new HTTP/1.1" 404 - "" "curl/7.58.0"

{"hint":"No function matches the given name and argument types. You might need to add explicit type casts.","details":null,"code":"42883","message":"function public.find_closest_new(json) does not exist"}

I am not sure what am I doing here , while I successfully wrote another function without join which is running fine. here it is : CREATE OR REPLACE FUNCTION public.find_closest(lat double precision,lon double precision) RETURNS table(device_id integer, latitude double precision, longitude double precision) as $$ SELECT deviceid, latitude, longitude FROM tc_positions WHERE ST_Distance_Sphere(ST_MakePoint(tc_positions.longitude, tc_positions.latitude),ST_MakePoint(lon,lat)) <= 2 * 1609.34 and devicetime >= timestamp '2020-01-30T13:56:02' and devicetime < timestamp '2020-01-30T15:56:02'; $$ LANGUAGE SQL;

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[linux-devil]

Done that.

[linux-devil]

Also I did double check on query and inner join etc. it works absolutely fine in psql Is there any problem with return type here ?

[steve-chavez]

@linux-devil Try calling the function like:

curl -X POST http://localhost:3000/rpc/find_closest_new \ 
-H 'Content-Type: application/json' -d '{"lat":12.976178,"lon":77.6434156}'

params=single-object is for when your function signature has a single json parameter.

[linux-devil]

Thanks a ton @steve-chavez this was really helpful , as my previous function was working , I wrote current version on the same line.