Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table example_table
(
  code text, 
  nr int, 
  value decimal,
  dt date
);
insert into example_table
values
('one', 1, 3.14, current_date),
('two', 2, 4.2, current_date-1);
2 rows affected

create table second_table
(
  id int, 
  some_number bigint,
  some_value numeric,
  name text
)
insert into second_table
values
(1, 42, 3.14, 'first'),
(2, 142, 6.28, 'second');
2 rows affected

with all_selects as (
  select table_schema, table_name, 'select '||string_agg(format('avg(%I) as %I', column_name, column_name), ', ')||format(' from %I.%I', table_schema, table_name) as query
  from information_schema.columns
  where table_name in ('example_table', 'second_table')
    and data_type in ('bigint', 'integer', 'double precision', 'smallint', 'numeric', 'real')
  group by table_schema, table_name
  limit 10
), all_aggregates as (
   select table_schema, table_name, 
          query_to_xml(query, true, true, '') as result
   from all_selects
)
select ag.table_schema, ag.table_name, r.column_name, nullif(r.average, '')::numeric as average
from all_aggregates ag
  cross join xmltable('/row/*' passing result
     columns column_name text path 'local-name()', 
             average text path '.') as r
             


table_schema
table_name
column_name
average


public
example_table
nr
1.5000000000000000

public
example_table
value
3.6700000000000000

public
second_table
id
1.5000000000000000

public
second_table
some_number
92.0000000000000000

public
second_table
some_value
4.7100000000000000

table_schema	table_name	column_name	average
public	example_table	nr	1.5000000000000000
public	example_table	value	3.6700000000000000
public	second_table	id	1.5000000000000000
public	second_table	some_number	92.0000000000000000
public	second_table	some_value	4.7100000000000000