Postgres 12.4 给出了函数不存在错误

Question

我在 postgres 12.4 中运行以下查询：

SELECT (TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name, 
pg_size_pretty(pg_table_size(table_name)) as table_size,  
pg_size_pretty(pg_indexes_size(table_name)) AS indexes_size, 
pg_size_pretty(pg_total_relation_size(table_name)) as total_size 
from information_schema.TABLES nowait 
where TABLE_SCHEMA='myschema' 
order by pg_total_relation_size(table_name) desc;

我收到以下错误消息：

ERROR:  function pg_table_size(information_schema.sql_identifier) does not exist
LINE 1: ..."."' || TABLE_NAME) as table_name, pg_size_pretty(pg_table_s...
^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

谁能告诉我我在这里到底错过了什么？我还想提一下，完全相同的查询在 postgres 版本 9.5 中运行良好。我在 postgres 12.4 文档中也找不到任何内容。欢迎任何想法/建议。谢谢！

Answer 1

根据 Postgres --general 列表中的此线程：

SELECT
pg_size_pretty(pg_table_size(quote_ident(table_name))),
pg_size_pretty(pg_indexes_size(quote_ident(table_name))) AS indexes_size,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) AS total_size
FROM
information_schema.tables
WHERE
table_schema = 'myschema'
;

您之前的查询从未处理过以下内容：

(TABLE_SCHEMA || '"."' || TABLE_NAME) as table_name

它只是使用table_name列中的值，该值以前是varchar，因此它在pg_table_size()中起作用。现在列类型已更改，您需要使用quote_ident()来正确转换它。仅供参考，上述内容也将在 9.5 中工作。