PLPGSQL Dynamic Query - ERROR:PL/pgSQL function inline_code_block lin

Question

I have been trying to run the following query for multiple tables:

SELECT count(*) as "number of pages",
       pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
       round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
       FROM pg_freespace('accounts');

What I have managed to write so far is:

do
$body$
declare
temprow record;
l_nop decimal;
l_pct decimal;
l_ratio decimal;
l_context text;
l_context_detail text;
tablex text;
begin
FOR temprow IN
    select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')/1024/1024 as size_mb from information_schema.tables
    where table_schema not in ('information_schema', 'pg_catalog') order by size_mb desc limit 100
    LOOP
begin
    raise notice 'for table %.% ', temprow.table_schema, temprow.table_name;
    execute E'SELECT count(*), pg_size_pretty(cast(avg(avail) as bigint)), round(100 * avg(avail)/8192 ,2)  FROM pg_freespace(\'$1.$2\')'  using temprow.table_schema, temprow.table_name into l_nop, l_pct, l_ratio;
EXCEPTION
WHEN others THEN GET STACKED DIAGNOSTICS
    l_context = PG_EXCEPTION_CONTEXT;
    RAISE NOTICE 'ERROR:%', l_context;
end;
    END LOOP;
end;
$body$; 

Yet, I get

NOTICE:  check1
NOTICE:  for table public.pg_hist_stat_statements_history
NOTICE:  ERROR:PL/pgSQL function inline_code_block line 18 at EXECUTE

I have tried different dynamic commands practices like format, but did not help. Also, tried to implement what I found in this question to get this point. What am I missing? Also, is it possible to write this PLPGSQL block as an SQL? My first thought was using the lateral join but pg_freespace pipelined function, so it did not work.

Thanks!

Answer 1

There is no reason to use dynamic SQL here. pg_freespace takes a table OID as its argument, which you can directly get from the first query. I'd use pg_catalog.pg_class though not information_schema.tables:

SELECT
  relnamespace::regnamespace AS table_schema,
  relname AS table_name,
  pg_relation_size(oid)/1024/1024 as size_mb,
  space.*
FROM pg_class,
LATERAL (
  SELECT
    count(*),
    pg_size_pretty(cast(avg(avail) as bigint)),
    round(100 * avg(avail)/8192 ,2)
  FROM pg_freespace(oid)
) AS space
WHERE relkind = 'r'
  AND relnamespace NOT IN ('information_schema'::regnamespace, 'pg_catalog'::regnamespace)
ORDER BY size_mb DESC
LIMIT 100

or more traditional with a GROUP BY instead of a subquery:

SELECT
  relnamespace::regnamespace AS table_schema,
  relname AS table_name,
  pg_relation_size(oid)/1024/1024 as size_mb,
  count(*),
  pg_size_pretty(cast(avg(avail) as bigint)),
  round(100 * avg(avail)/8192 ,2)
FROM pg_class, LATERAL pg_freespace(oid)
WHERE relkind = 'r'
  AND relnamespace NOT IN ('information_schema'::regnamespace, 'pg_catalog'::regnamespace)
GROUP BY relnamespace, relname, oid
ORDER BY size_mb DESC
LIMIT 100