postgresqlplpgsqldynamic-queries

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


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!


Solution

  • 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