Re: Information schema sql_identifier

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(dot)klaver(at)aklaver(dot)com