0

This is the required query result from a function:

[{"id":1,"task":"Finish Task ONE"},{"id":2,"task":"Finish Task TWO"}]

And it is produced correctly in pgAdmin4 by: SELECT api.add_them(1,2) in 4.

However, when the function is invoked with Windows Curl or by an Android Studio app it produces:

[{"add_them":[{"id":1,"task":"Finish Task ONE"},{"id":2,"task":"Finish Task TWO"}]}]

So the function name is being included as a wrapper around the body.

The Windows curl script is: 

curl http://localhost:3000/rpc/add_them -X POST ^
     -H "Authorization: Bearer <My JWT Token>"   ^
     -H "Content-Type: application/json" ^
     -H "Prefer: return=representation" ^
     -d "{\"a\": 1, \"b\": 2}"

The Function is:

CREATE OR REPLACE FUNCTION api.add_them(
a integer,
b integer)
RETURNS setof json
LANGUAGE 'plpgsql'

COST 100
VOLATILE 
ROWS 1000

AS $$

BEGIN
RETURN QUERY 
SELECT array_to_json(array_agg(row_to_json(t)))
FROM ( select id, task from api.todos
       where id >= a and id <= b) t;
END;  $$;

ALTER FUNCTION api.add_them(a integer, b integer)
OWNER TO postgres;
Nasher
  • 1

1 Answers1

0

PostgREST uses a schema cache that can get stale if you add new functions and don't refresh it.

To refresh the schema cache, restart PostgREST or reload it with a SIGUSR1(killall -SIGUSR1 postgrest).

You can see more details about this in: https://postgrest.org/en/v6.0/admin.html#schema-reloading.

Also check the important note(in green) at the end of this section: https://postgrest.org/en/v6.0/api.html#stored-procedures

Steve Chavez
  • 931
  • 10
  • 13
  • I had tried your suggested solution. Apologies for not stating I am using Postgrest. Thank you. – Nasher Dec 19 '19 at 15:50
  • I believe he's calling the function OK, but PostgREST seems to wrap function results as an array property (maybe it's assuming rows) whose name is the function name. – grantwparks Jan 16 '21 at 04:05