Re: Information schema sql_identifier

On 12/22/20 4:39 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> So how does one go about using a table name from
>> information_schema.tables in pg_table_size()?
> 
> You want something like
> 
> select pg_table_size(quote_ident(table_schema)||'.'||quote_ident(table_name))
>    from information_schema.tables;
> 
> I imagine that the failures you got are a consequence of having
> some table names that aren't valid unless quoted (ie contain
> spaces, funny characters, etc).  In a general-purpose query,
> you can't ignore the schema name either.
> 
> I might be more excited about v12's failure to provide an implicit
> cast to regclass if there were any prospect of queries like this
> working in a bulletproof way without accounting for schema names
> and funny characters.  But there isn't, so the query shown in SO
> is a house of cards to start with.  When you do it right, with
> quote_ident() or format(), no special casting is needed.

Thanks, that pushed me in right direction.

I see now the previous query worked because the alias table_name and the 
column table_name where the same and the column previously was a 
varchar. This meant the pg_table_size() was actually working on the 
column value not the concatenated value.

So the query can be simplified to:

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 = 'public'
;


> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com