update_languages_translations_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', 'Replacement of helpers.unaccent_text with helpers.normalize_text and better search of translations', _component := 'languages_translations');


create or replace function helpers.calculate_translation_fields() returns trigger as
$$
begin
	if tg_op = 'INSERT' or tg_op = 'UPDATE' then
		new.ua_search_data = helpers.normalize_text(new.value);
		new.ts_search_data = to_tsvector(helpers.calculate_ts_regconfig(new.language_code), new.value);
		return new;
	end if;
end;
$$ language plpgsql;


odlozim si 


create or replace function search_translations(_display_language_code text, _page integer DEFAULT 1, _page_size integer DEFAULT 10, _tenant_id integer DEFAULT NULL::integer, _language_code text DEFAULT NULL::text, _data_group text DEFAULT NULL::text, _data_object_code text DEFAULT NULL::text, _data_object_id bigint DEFAULT NULL::bigint, _value_search_text text DEFAULT NULL::text) returns TABLE(__translation_id integer, __tenant_id integer, __tenant_title text, __language_code text, __language_value text, __data_group text, __data_object_code text, __data_object_id bigint, __value text, __total_items bigint)
	language plpgsql
as $$
declare
begin

	return query
		with filtered_rows
					 as (select t.translation_id
										, count(*) over () as total_items
							 from translation t
							 where (_tenant_id is null
								 or tenant_id = _tenant_id)
								 and (helpers.is_empty_string(_language_code)
								 or language_code = _language_code)
								 and (helpers.is_empty_string(_data_group)
								 or lower(data_group) like lower(_data_group) || '%')
								 and (helpers.is_empty_string(_data_object_code)
								 or lower(data_object_code) like lower(_data_object_code) || '%')
								 and (_data_object_id is null
								 or data_object_id = _data_object_id)
								 and (helpers.is_empty_string(_value_search_text)
								 or
											helpers.normalize_text(value) like '%' || helpers.normalize_text(_value_search_text) || '%')
							 order by data_group, data_object_code, data_object_id nulls last, language_code
							 offset ((_page - 1) * _page_size) limit _page_size)
		select t.translation_id
				 , t.tenant_id
				 , te.title
				 , t.language_code
				 , tl.value
				 , t.data_group
				 , t.data_object_code
				 , t.data_object_id
				 , t.value
				 , total_items
		from filtered_rows fr
					 inner join translation t on fr.translation_id = t.translation_id
					 inner join auth.tenant te on t.tenant_id = te.tenant_id
					 left join translation tl on tl.language_code = _display_language_code and tl.data_group = 'language' and
																			 tl.data_object_code = t.language_code
		order by data_group, data_object_code, data_object_id nulls last, language_code;
end;
$$;



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