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.kla...@aklaver.com


Reply via email to