On 12/22/20 11:21 PM, Laurenz Albe wrote:
On Tue, 2020-12-22 at 16:07 -0800, Adrian Klaver wrote:
This came up in this SO question:
https://stackoverflow.com/questions/65416748/postgres-12-4-gives-function-does-not-exists-error
Where the query is:
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;
And the error is:
"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..."
I don't see the problem.
Cast "table_name" and "table_schema" to "text" wherever it occurs.
SELECT pg_table_size(table_name::text) from information_schema.tables
where table_schema = 'public';
ERROR: invalid name syntax
Per Tom's post this does not cover special cases of identifiers. The
above was run on my test database that has all manner of weird things it.
So:
SELECT table_name from information_schema.tables where table_schema =
'public' and table_name ilike 'space%';
table_name
-------------
space table
SELECT pg_table_size('space table') ;
ERROR: invalid name syntax
SELECT pg_table_size(quote_ident('space table')) ;
pg_table_size
---------------
8192
Yours,
Laurenz Albe
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
