Deploy PostgREST stored procedures with curl

This post is an appendix for PostgREST API Guide/stored-procedures. Step-by-step:

1. Deploy SQL examples

Run at psql or similar:

CREATE FUNCTION add_them(a integer, b integer) RETURNS integer AS $$
 SELECT a + b;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION add_them2(a integer, b integer) -- idem but not IMMUTABLE
RETURNS integer AS $$
 SELECT a + b;  
$$ LANGUAGE SQL;

CREATE FUNCTION add_them3(integer,integer) -- idem but without parameter names
RETURNS integer AS $$
 SELECT $1 + $2;  
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION add_them_json(p JSON) -- idem but JSON parameter
RETURNS integer AS $$
 SELECT ($1->>'a')::int + ($1->>'b')::int;  
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION add_them_jsonb(p JSONb) -- idem but JSONb parameter
RETURNS integer AS $$
 SELECT ($1->>'a')::int + ($1->>'b')::int;  
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE FUNCTION mult_them(param json) RETURNS int AS $$
  SELECT (param->>'x')::int * (param->>'y')::int
$$ LANGUAGE SQL IMMUTABLE STRICT;

Test by SQL:

SELECT add_them(1,2); -- returns 3
SELECT add_them2(1,2); -- returns 3
SELECT add_them3(1,2); -- returns 3
SELECT add_them_json('{"a":1,"b":2}'); -- returns 3
SELECT mult_them('{"x":2,"y":3}'); -- returns 6

2. Cache refresh

Run at server (by ssh or similar)

• for dockered PostgREST
  • Use docker kill --signal=HUP <container id or name> to reload. Or just restart the container.
• for commom PostgREST, schema reloading: killall -SIGUSR1 postgrest

3. Tests with curl

Use -i to see headers.

1. curl "http://localhost:3000/rpc/add_them?a=1&b=2" returns 3.

2. curl -H "Content-Type: application/json" --request POST --data '{"a": 1, "b": 2}' http://localhost:3000/rpc/add_them returns 3. Same as:
   curl -i -H "Accept application/json" -H "Accept-Charset: utf-8" -H "Content-Type: application/json" --request POST --data '{"a": 1, "b": 2}' http://localhost:3000/rpc/add_them

3. curl "http://localhost:3000/rpc/add_them2?a=1&b=2" returns 3.

4. curl "http://localhost:3000/rpc/add_them3?a=1&b=2" returns ERROR 42883.

5. curl "http://localhost:3000/rpc/add_them_json?a=1&b=2" returns ERROR 42883.

6. curl -H "Content-Type: application/json" --request POST --data '{"a": 1, "b": 2}' http://localhost:3000/rpc/add_them_json" returns ERROR 42883.

7. curl "http://localhost:3000/rpc/add_them_jsonb?a=1&b=2" returns ERROR 42883.

8. error curl -H "Content-Type: application/json" --request POST --data '{"x":2,"y":3}' "http://localhost:3000/rpc/mult_them"

9. success! curl -H "Content-Type: application/json" -H "Prefer: params=single-object" --request POST --data '{"x":2,"y":3}' "http://localhost:3000/rpc/mult_them" returns 6!