update_common-helpers_v1-1.sql

/*
 GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=0&v=1&c=c&f=ANSI%20Shadow&t=STAGE%20FUNCS

 SUB GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=1&v=1&c=c&f=Banner3&t=permissions

 */

select *
from start_version_update('1.1', 'Rename of methods, new functions for jsonb operations', _component := 'common_helpers',
													_description := 'Few methods renamed to better describe what they do, new methods to normalize and hash jsonb');


/***
 *    ███╗   ██╗ ██████╗ ██████╗ ███╗   ███╗ █████╗ ██╗     ██╗███████╗███████╗    ████████╗███████╗██╗  ██╗████████╗
 *    ████╗  ██║██╔═══██╗██╔══██╗████╗ ████║██╔══██╗██║     ██║╚══███╔╝██╔════╝    ╚══██╔══╝██╔════╝╚██╗██╔╝╚══██╔══╝
 *    ██╔██╗ ██║██║   ██║██████╔╝██╔████╔██║███████║██║     ██║  ███╔╝ █████╗         ██║   █████╗   ╚███╔╝    ██║
 *    ██║╚██╗██║██║   ██║██╔══██╗██║╚██╔╝██║██╔══██║██║     ██║ ███╔╝  ██╔══╝         ██║   ██╔══╝   ██╔██╗    ██║
 *    ██║ ╚████║╚██████╔╝██║  ██║██║ ╚═╝ ██║██║  ██║███████╗██║███████╗███████╗       ██║   ███████╗██╔╝ ██╗   ██║
 *    ╚═╝  ╚═══╝ ╚═════╝ ╚═╝  ╚═╝╚═╝     ╚═╝╚═╝  ╚═╝╚══════╝╚═╝╚══════╝╚══════╝       ╚═╝   ╚══════╝╚═╝  ╚═╝   ╚═╝
 *
 */

drop function if exists helpers.unaccent_text(_text text);
drop function if exists helpers.unaccent_text(_text text, _lower_text boolean);

drop function if exists helpers.normalize_text(_text text);

create function helpers.normalize_text(_text text, _lower_text boolean DEFAULT true) returns text
	immutable
	strict
	parallel safe
	cost 0.1
	language sql
as
$$
select case when _lower_text then lower(ext.unaccent(_text)) else ext.unaccent(_text) end;
$$;


/***
 *         ██╗███████╗ ██████╗ ███╗   ██╗██████╗
 *         ██║██╔════╝██╔═══██╗████╗  ██║██╔══██╗
 *         ██║███████╗██║   ██║██╔██╗ ██║██████╔╝
 *    ██   ██║╚════██║██║   ██║██║╚██╗██║██╔══██╗
 *    ╚█████╔╝███████║╚██████╔╝██║ ╚████║██████╔╝
 *     ╚════╝ ╚══════╝ ╚═════╝ ╚═╝  ╚═══╝╚═════╝
 *
 */

create or replace function helpers.compute_jsonb_hash(_data jsonb, _normalize_text bool default false,
																											_field_value_separator text default ':',
																											_fields_separator text default '|')
	returns text
	strict
	immutable
	cost 0.1
	language sql
as
$$
with sorted_values as (select key,
															case
																when _normalize_text then helpers.normalize_text(value::text)::jsonb
																else value end as value
											 from jsonb_each(_data)
											 order by key)
select sha256(convert_to(string_agg(concat_ws(_field_value_separator, key, value), _fields_separator), 'UTF8')::bytea)
from sorted_values;
$$;

create or replace function helpers.normalize_jsonb_values(_data jsonb)
	returns jsonb
	strict
	immutable
	cost 0.1
	language sql
as
$$
with normalized_values as (select key, helpers.normalize_text(value::text) normalized_value
													 from jsonb_each(_data)
													 order by key)
select jsonb_object_agg(key, normalized_value)
from normalized_values;
$$;

select *
from stop_version_update('1.1', _component := 'common_helpers');