sql-helpers.sql

create or replace function public.json_is_array(j json) returns boolean
    language plpgsql
as
$$
BEGIN
  RETURN exists(SELECT regexp_matches(j :: VARCHAR, '\[.*\]'));
END;
$$;

create or replace function public.json_is_array(j character varying) returns boolean
    language plpgsql
as
$$
BEGIN
  RETURN exists(SELECT regexp_matches(j, '\[.*\]'));
END;
$$;

create or replace function public.json_is_empty(_json json) returns boolean
    language plpgsql
as
$$
BEGIN
    return NOT EXISTS (SELECT 1 FROM json_object_keys(_json));
END;
$$;


create or replace function public.json_array_values_as_text(_j json, _key text) returns text[]
    immutable
    language sql
as
$$
	SELECT array_agg(elem->>_key)
	FROM json_array_elements(_j) AS x(elem)
$$;


create or replace function public.jsonb_array_values_as_text(_j jsonb, _key text) returns text[]
    immutable
    language sql
as
$$
	SELECT array_agg(elem->>_key)
	FROM jsonb_array_elements(_j) AS x(elem)
$$;


create or replace function public.jsonb_is_array(j jsonb) returns boolean
    language plpgsql
as
$$
BEGIN
  RETURN exists(SELECT regexp_matches(j :: VARCHAR, '\[.*\]'));
END;
$$;


create or replace function public.require(params json, name text) returns void
    language plpgsql
as
$$
BEGIN
  if params->>name is null then raise exception '%: поле пустое', name  USING ERRCODE='P0001'; end if;
END;
$$;



create or replace function public.requireBigint(params json, key text) returns bigint
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return params->>key;
END;
$$;


create or replace function public.requireBoolean(params json, key text) returns boolean
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return coalesce((params->>key)::BOOLEAN,false);
END;
$$;


create or replace function public.requireDate(params json, key text) returns timestamp without time zone
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return (params->>key)::TIMESTAMP;
END;
$$;

create or replace function public.requireDouble(params json, key text) returns double precision
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return (params->>key)::double precision;
END;
$$;


create or replace function public.requireInt(params json, key text) returns integer
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return params->>key;
END;
$$;


create or replace function public.requireJson(params json, key text) returns json
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return (params->>key)::json;
END;
$$;

create or replace function public.requireJsonb(params json, key text) returns jsonb
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return (params->>key)::jsonb;
END;
$$;

create or replace function public.requireJsonb(params jsonb, key text) returns jsonb
    language plpgsql
as
$$
BEGIN
  perform public.require(params::json, key);
  return (params->>key)::jsonb;
END;
$$;


create or replace function public.requireNumeric(params json, key text) returns numeric
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return (params->>key)::NUMERIC(10,2);
END;
$$;


create or replace function public.requireText(params json, key text) returns text
    language plpgsql
as
$$
BEGIN
  perform public.require(params,key);
  return trim(params->>key);
END;
$$;


create or replace function public.optionalBigint(params json, key text) returns bigint
    language plpgsql
as
$$
BEGIN
  return params->>key;
END;
$$;


create or replace function public.optionalBoolean(params json, key text) returns boolean
    language plpgsql
as
$$
BEGIN
  return coalesce((params->>key)::BOOLEAN,false);
END;
$$;


create or replace function public.optionalDate(params json, key text) returns timestamp without time zone
    language plpgsql
as
$$
BEGIN
  return (params->>key)::TIMESTAMP;
END;
$$;


create or replace function public.optionalDouble(params json, key text) returns double precision
    language plpgsql
as
$$
BEGIN
  return (params->>key)::double precision;
END;
$$;


create or replace function public.optionalInt(params json, key text) returns integer
    language plpgsql
as
$$
BEGIN
  return params->>key;
END;
$$;


create or replace function public.optionalJson(params json, key text) returns json
    language plpgsql
as
$$
BEGIN
  return (params->>key)::json;
END;
$$;


create or replace function public.optionalJsonb(params json, key text) returns jsonb
    language plpgsql
as
$$
BEGIN
  return (params->>key)::jsonb;
END;
$$;


create or replace function public.optionalJsonb(params jsonb, key text) returns jsonb
    language plpgsql
as
$$
BEGIN
  return (params->>key)::jsonb;
END;
$$;


create or replace function public.optionalNumeric(params json, key text) returns numeric
    language plpgsql
as
$$
BEGIN
  return (params->>key)::NUMERIC(10,2);
END;
$$;


create or replace function public.optionalText(params json, key text) returns text
    language plpgsql
as
$$
BEGIN
  return trim(params->>key);
END;
$$;


create or replace function public.optionalUuid(params json, key text, _default uuid) returns uuid
    language plpgsql
as
$$
DECLARE
    _val uuid;
BEGIN
    _val = params ->> key;
    return coalesce(_val, _default);
END;
$$;