Re: bug regclass::oid

"Peter J. Holzer" <hjp-pgsql(at)hjp(dot)at> writes:
> On 2019-06-16 18:03:02 +0200, John Mikel wrote:
>> here is my query 
>>  select   A.table_name  as "table_name",A.domain_name as "domain",
>>  format_type(c.atttypid, c.atttypmod)  AS data_type ,A.column_name as
>> "column_name",
>>   A.is_nullable as "nullable",A.column_default as "default"
>>   from information_schema.columns A inner  join pg_attribute c  on 
>> a.table_name::regclass::oid=c.attrelid
>>   where  a.table_schema in (select current_schema()) and  a.column_name =
>> c.attname ;
>>
>> if i run this query in any database contain at least one table with space in
>> their name , an error occurred

> You have to quote the table name [ with quote_ident ]

Note that that's still unreliable, because it's not considering the
possibility of duplicate table names in different schemas. You could
do something like

where
(quote_ident(a.table_schema) || '.' || quote_ident(a.table_name))::regclass = c.attrelid

If that seems awfully brute-force, you're right, but I think it's
self-inflicted damage from trying to mix two different levels of
abstraction -- namely, the information_schema and the underlying
native PG catalogs. I'd suggest recasting this as a join between
pg_catalog and pg_attribute, which would make the join condition
just "where c.oid = a.attrelid".

regards, tom lane