postgresql PLPGSQL动态查询-错误：PL/pgSQL函数inline_code_block行

6jjcrrmo 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(437)

我一直在尝试对多个表运行以下查询：

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');

到目前为止，我写的是：

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$;

然而，我得到了

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

我尝试了不同的动态命令实践，如格式，但没有帮助。另外，尝试实现我在这个question中找到的东西来获得这一点。我错过了什么？另外，是否可以将此PLPGSQL块写入SQL？我的第一个想法是使用lateral连接，但pg_freespace管道函数，所以它没有工作。
谢谢！

postgresql

来源：https://stackoverflow.com/questions/76566902/plpgsql-dynamic-query-errorpl-pgsql-function-inline-code-block-lin

1条答案

按热度按时间

8cdiaqws1#

这里没有理由使用动态SQL。pg_freespace接受一个表OID作为其参数，您可以直接从第一个查询中获得该参数。我会使用pg_catalog.pg_class，而不是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

或者更传统的使用GROUP BY而不是子查询：

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

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql PLPGSQL动态查询-错误：PL/pgSQL函数inline_code_block行

1条答案

相关问题

热门标签

最新问答