From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Information schema sql_identifier |
Date: | 2020-12-23 01:22:42 |
Message-ID: | 9511a051-82a4-858a-2212-4faa54e92350@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/22/20 4:39 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-12-23 07:21:54 | Re: Information schema sql_identifier |
Previous Message | Adrian Klaver | 2020-12-23 01:03:38 | Re: Information schema sql_identifier |