Postgres common queries / triggers / functions

pg.sql

-- sua (set updated at): set updated_at = now() when updating row
create or replace function sua() returns trigger language plpgsql as $$
begin
  new.updated_at = now()::timestamptz(0);
  return new;
end; $$;

-- asu (array sort unique): keep array values sorted and unique
create or replace function asu (anyarray) returns anyarray language sql as $$
  select array(select distinct $1[s.i] from generate_series(array_lower($1,1), array_upper($1,1)) as s(i) order by 1);
$$;

-- ids_in: check if all ids in an array are present in the referenced table
create or replace function ids_in(ids bigint[], ref_table text, ref_column text) returns boolean language plpgsql as $$
  declare
    query text;
    result boolean;
  begin
      query := format('select not exists (select 1 from unnest(%s::bigint[]) as ex_id left join %I as ref on ref.%I = ex_id where ref.%I is null)', quote_literal(ids), ref_table, ref_column, ref_column);
      execute query into result;
    return result;
end;
$$;

create or replace function strings_to_numbers(text[]) returns int[] language sql immutable parallel safe as $$
  select array (
    select regexp_replace(trim(elem), '[^\d].*$', '')::int
    from unnest($1) elem
    where regexp_replace(trim(elem), '[^\d].*$', '') != ''
  )
$$;

/** INDEXES ANALYSIS */
select
  t.relname as table_name,
  pg_size_pretty(sum(pg_total_relation_size(i.indexrelid))) as total_index_size
from pg_stat_user_indexes as i
join pg_class t on (i.relname = t.relname)
group by t.relname
order by sum(pg_total_relation_size(i.indexrelid)) desc;

select
  idx.relname,
  tbl.seq_scan as n_full_scans,
  idx.indexrelname,
  tbl.n_live_tup as n_rows,
  idx.idx_scan as n_indexed_scans,
  pg_size_pretty(pg_total_relation_size(idx.indexrelid)) as index_size
from pg_stat_user_indexes as idx
join pg_stat_user_tables as tbl on (idx.relname = tbl.relname)
order by pg_total_relation_size(indexrelid) desc;