Search Postgresql Archives

Re: bug regclass::oid

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Peter J. Holzer" <hjp-pgsql@xxxxxx> 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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux