JSON-RPC interface for PostgreSQL 9+ JSONb frameworks

mod-jsonrpc.sql

/**
 * JSON-RPC interface. Mini-lib, independent.
 * Works fine with REST.
 * See http://www.jsonrpc.org/specification  (only error/code convention have little change)
 * Free error string code:
 *     "$integer" = "$integer.0" = HTTP status code (only REST)
 *     "$integer.$integer" = status and internal error code (DEFAULT)
 *     use function rpc.errcode_merge() to merge default error reference and add internal part.
 */

 DROP SCHEMA IF EXISTS rpc CASCADE;
 CREATE SCHEMA rpc;  -- for generic RPC library


/* - - - - - - - - -
   - - - - - - - - -

 Some tips about the conventions:
  - good SQL framework use JSONb (a first class citizen), not JSON.
  - RPC is for external interface, so JSON most used for external world, but you can replace returns to JSONb.
  - As also REST interface, we modify error-code conventions of JSON-RPC:
    not integer but a float, integer part as HTTP-state code, decimal part for internal error codes.

 Usagem examples for ret() function:
   SELECT rpc.ret(null::jsonb);
   SELECT rpc.ret(null::jsonb, 22);
   SELECT rpc.ret('"Hello!"'::jsonb);
   SELECT rpc.ret('{"x":1,"y":2}'::jsonb);
   SELECT rpc.ret(null::jsonb, 0, '{"neg":null}');
   SELECT rpc.ret('{"test":{"code":501.120,"message":"etc"}}'::jsonb, 0, '{"err":null}');
   SELECT rpc.ret('{}'::jsonb, 0, '{"null":null}');
   SELECT rpc.ret('-1'::jsonb, 0);

 Usagem examples for return interpretation as error:
   SELECT rpc.ret('-1'::jsonb, 0, '{"neg":null}');
   SELECT rpc.ret('-5'::jsonb, 0, '{"neg":null}');
   SELECT rpc.ret(null::jsonb, 0, '{"null":null}');
   SELECT rpc.ret(null::jsonb, 0, '{"null":[601.120,"etc"]}');
   SELECT rpc.ret('{"error":{"code":501.120,"message":"etc"}}'::jsonb, 0, '{"err":null}');
   SELECT rpc.ret(null::jsonb, 0, '{"emp":null}');
   SELECT rpc.ret('{}'::jsonb, 0, '{"emp":null}');
   SELECT rpc.ret('0'::jsonb, 0, '{"zero":null}');

 Usagem examples for dynamic error function, using ret()
   SELECT rpc.ret('{"code":500.102,"message":"stack overflow"}'::jsonb,22,null,true);

 Usagem examples for error() function:
   SELECT rpc.error(500.110,'Lorem ipsum dolor sit amet, consectetur adipiscing elit.',3);

 Wrong usage (seems valid but is not) examples:
   SELECT rpc.ret('{"error":2}'::jsonb, 0, '{"err":null}');  -- need complete error object

- - - - - - - - -
- - - - - - - - - */

/**
 * Take input as float-like string ("int.int") and int, merge int (adding it as decimal-like part).
 * So, take "int1.int2" and "int3", returning int1.(int2+int3)
 */
CREATE or replace FUNCTION rpc.errcode_merge(
  text,  -- int or "int.int" code
  text   -- positive or negative integer
) RETURNS text AS $f$
  SELECT p1::text ||'.'|| p2::text
  FROM (
    SELECT x[1]::int as p1,  COALESCE(x[2]::int,0) + COALESCE(replace($2,'-','')::int,0) as p2
    FROM (SELECT regexp_split_to_array(COALESCE($1,'0.0'),'\.')) t1(x)
  ) t2
$f$ language SQL IMMUTABLE;
CREATE FUNCTION rpc.errcode_merge(text,int) RETURNS text AS $wrap$ SELECT rpc.errcode_merge($1,$2::text) $wrap$ language SQL IMMUTABLE;
CREATE FUNCTION rpc.errcode_merge(jsonb,jsonb) RETURNS text AS $wrap$ SELECT rpc.errcode_merge($1::text,$2::text) $wrap$ language SQL IMMUTABLE;


/**
 * Standard return in JSON-RPC jargon. No "Notification concept" here.
 * For usual return:  rpc.ret(jRet,Id)
 * For return-with-flag:  rpc.ret(jRet,Id,HowToInterpretRet)
 * For undefined return: rpc.ret(jRetOrError,Id,NULL,IsRet)
 * Valid interpretations: a JSONb object as {"name1":[flatCode,Message],"name2":[etc]}
 *  where name can be 'null', 'neg', 'emp', 'err' or 'zero';
 *  for instance  '{"null":[500.101,"returinng null"]}'::jsonb.
 */
CREATE or replace FUNCTION rpc.ret(
	p_ret JSONb,               	-- 1. The return object
	p_async_id int DEFAULT 0, 	-- 2. Nothing (0) or ID for asynchronous calls.
	p_onRetError JSONb DEFAULT NULL, 	-- 3. Nothing (null) or flag to analyse $1.
  p_isError boolean DEFAULT false 	-- 4. Flag to change $1 interpretatin (error behaviour).
) RETURNS JSON AS $f$
	SELECT json_build_object(
		'jsonrpc','2.0',
		'id',COALESCE($2,0),
		CASE WHEN p_isError OR NOT(isNormal) THEN 'error' ELSE 'result' END,
		CASE
      WHEN p_isError OR isNormal THEN $1::json
      ELSE jsonb_build_object('code',df->0, 'message',df->1)::json
    END
		)
	FROM ( -- t
		SELECT df, df IS NULL AS isNormal -- not error
		FROM (  -- t2
			SELECT CASE  -- confere se $4 existe e se $1 é erro
							WHEN df1 IS NULL THEN NULL
              WHEN df1?'err'  AND $1->>'error' IS NOT NULL THEN $1->'error'
							WHEN df1?'neg'  AND ($1#>>'{}')::float<1  THEN
                jsonb_build_array( rpc.errcode_merge(df1->'neg'->0,$1),  df1->'neg'->1 )
							WHEN df1?'zero' AND ($1#>>'{}')::float=0  THEN df1->'zero'
              WHEN df1?'null' AND ($1 IS NULL OR jsonb_typeof($1)='null') THEN df1->'null'
              WHEN df1?'emp'  AND ($1 IS NULL OR jsonb_typeof($1)='null' OR $1='{}'::jsonb OR ($1#>>'{}')='') THEN df1->'emp'
							ELSE NULL -- indicate that is not an error
						END AS df
			FROM ( -- tdef
				SELECT lib.preserve_notnull(
						'{"null":[500.101,"returing null"],"emp":[500.102,"returing empty"],"zero":[500.103,"returing zero"],"neg":[500.110,"result is an error code"]}'::jsonb,
						p_onRetError, false) AS df1
				) tdef
		) t2
	) t
$f$ language SQL IMMUTABLE;


CREATE FUNCTION rpc.ret(
  JSON, int DEFAULT 0, JSONb DEFAULT NULL, boolean DEFAULT false
) RETURNS JSON AS $wrap$
  SELECT rpc.ret($1::jsonb, $2, $3, $4)
$wrap$ language SQL IMMUTABLE;

----------

CREATE FUNCTION rpc.error(
  int,    -- 1. REST HTTP status code, as https://en.wikipedia.org/wiki/List_of_HTTP_status_codes
  int,    -- 2. internal error code, as your framework conventions.
  text,   -- 3. error message
  int DEFAULT 0  -- 4. p_async_id
) RETURNS JSON AS $wrap$
	SELECT rpc.ret(
    jsonb_build_object('code',($1::text||'.'||$2::text)::float, 'message',$3),
    $4,  NULL, true
  )
$wrap$ language SQL IMMUTABLE;

CREATE FUNCTION rpc.error(
  float,  -- error code
  text,   -- error message
  int DEFAULT 0  -- p_async_id
) RETURNS JSON AS $wrap$
	SELECT rpc.ret(jsonb_build_object('code',$1, 'message',$2), $3, NULL, true)
$wrap$ language SQL IMMUTABLE;

Hi, how is the function lib.preserve_notnull?