Search Postgresql Archives

Re: bug regclass::oid

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

 



On 2019-06-16 18:03:02 +0200, John Mikel wrote:
> hi again 
> 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

Note that Adrian had the name enclosed in double quotes:

> Le jeu. 13 juin 2019 à 17:33, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> a
> écrit :
> 
>>     Try:
>> 
>>     SELECT '"this is test"'::regnamespace::oid;  not working ;
>> 
>>     Same for table name. As example:
>> 
>>     select '"space table"'::regclass;

You don't do that you just try to use a.table_name as is. But
'space table'::regclass doesn't work.

You have to quote the table name:

hjp=> select table_schema, table_name::regclass, column_name from information_schema.columns where table_name like '% %'; 
ERROR:  invalid name syntax
Time: 5.794 ms
hjp=> select table_schema, quote_ident(table_name)::regclass, column_name from information_schema.columns where table_name like '% %'; 
╔══════════════╤═════════════╤═════════════╗
║ table_schema │ quote_ident │ column_name ║
╟──────────────┼─────────────┼─────────────╢
║ public       │ "foo bar"   │ id          ║
╚══════════════╧═════════════╧═════════════╝
(1 row)

	hp

-- 
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@xxxxxx         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment: signature.asc
Description: PGP signature


[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