Exception

.sql

CREATE OR REPLACE FUNCTION shared.raise_exception(
    value        anyelement,
    message      text default 'Unhandled value',
    detail       text default null,
    hint         text default 'See value (type %s) in detail as JSON',
    errcode      text default 'raise_exception',
    "constraint" text default null,
    "schema"     text default null,
    "table"      text default null,
    "column"     text default null
)
    RETURNS ANYELEMENT
    IMMUTABLE
    PARALLEL SAFE
    LANGUAGE plpgsql
    SET search_path = ''
AS
$$
BEGIN
    RAISE EXCEPTION USING
        message = coalesce(message, 'Unhandled value'),
        detail  = coalesce(detail, coalesce(to_jsonb(value), 'null'::jsonb)::text),
        hint    = format(coalesce(hint, 'See value (type %s) in detail as JSON'), pg_typeof(value)),
        errcode = coalesce(errcode, 'raise_exception'/*ERRCODE_RAISE_EXCEPTION (P0001)*/),
        constraint  = coalesce("constraint", ''),
        schema      = coalesce("schema", ''),
        table       = coalesce("table", ''),
        column      = coalesce("column", ''),
        datatype    = pg_typeof(value)::text;
    RETURN null::anyelement;
END;
$$;

CREATE OR REPLACE FUNCTION shared.test_raise_exception(
    value  anyelement,
    message      text default 'Unhandled value',
    detail       text default null,
    hint         text default 'See value (type %s) in detail as JSON',
    errcode      text default 'raise_exception',
    "constraint" text default null,
    "schema"     text default null,
    "table"      text default null,
    "column"     text default null
)
    RETURNS jsonb
    IMMUTABLE
    PARALLEL SAFE
    LANGUAGE plpgsql
    SET search_path = ''
AS
$$
DECLARE
  exception_sqlstate text;
  exception_message text;
  exception_context text;
  exception_detail text;
  exception_hint text;
  exception_datatype text;
  exception_table text;
BEGIN
  PERFORM shared.raise_exception(value, message, detail, hint, errcode, "constraint", "schema", "table", "column");
  RETURN 'null'::jsonb;
EXCEPTION WHEN others THEN
  GET STACKED DIAGNOSTICS --https://postgrespro.ru/docs/postgresql/14/plpgsql-control-structures#PLPGSQL-ERROR-TRAPPING
    exception_sqlstate := RETURNED_SQLSTATE,
    exception_message  := MESSAGE_TEXT,
    exception_context  := PG_EXCEPTION_CONTEXT,
    exception_detail   := PG_EXCEPTION_DETAIL,
    exception_hint     := PG_EXCEPTION_HINT,
    exception_datatype := PG_DATATYPE_NAME;
    exception_table    := TABLE_NAME;
  RETURN jsonb_build_object('sqlstate', exception_sqlstate, 'message', exception_message, 'context', exception_context, 'detail', exception_detail, 'hint', exception_hint, 'datatype', exception_datatype, 'table', exception_table);
END;
$$;