bug regclass::oid

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

bug regclass::oid

John Mikel
 hi ,
 i am here to tell you that this  test in query is not working when the
 table name in the database or schema name   contain space
 a.table_name::regclass=b.attrelid 
 
 a is information_schema.columns
 b is pg_attribute
 
 trying this in two different databases the first database contain table
 with space in his name (Problem when running query)
 the second no ( work fine)
 
 the same problme if you get Oid from schema name.
 SELECT 'public'::regnamespace::oid;  work ;
 create schema " this is test" ;
 SELECT 'this is test'::regnamespace::oid;  not working ;
 
 i have question how use join between  information_schema.columns and
 pg_attribute ? thanks
 
regards

Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

Adrian Klaver-4
On 6/13/19 8:14 AM, John Mikel wrote:

> * hi ,
>   i am here to tell you that this  test in query is not working when the
>   table name in the database or schema name   contain space
>   a.table_name::regclass=b.attrelid
>
>   a is information_schema.columns
>   b is pg_attribute
>
>   trying this in two different databases the first database contain table
>   with space in his name (Problem when running query)
>   the second no ( work fine)
>
>   the same problme if you get Oid from schema name.
>   SELECT 'public'::regnamespace::oid;  work ;
>   create schema " this is test" ;
>   SELECT 'this is test'::regnamespace::oid;  not working ;

Try:

SELECT '"this is test"'::regnamespace::oid;  not working ;

Same for table name. As example:

select '"space table"'::regclass;
  regclass
---------------
  "space table"
(1 row)

>
>   i have question how use join between  information_schema.columns and
>   pg_attribute ? thanks
>
> regards*


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

John Mikel
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
if i run this query in other database work fine 
I tested this on pg 11.1  , pg 10.3, pg 9.6



Le jeu. 13 juin 2019 à 17:33, Adrian Klaver <[hidden email]> a écrit :
On 6/13/19 8:14 AM, John Mikel wrote:
> * hi ,
>   i am here to tell you that this  test in query is not working when the
>   table name in the database or schema name   contain space
>   a.table_name::regclass=b.attrelid
>
>   a is information_schema.columns
>   b is pg_attribute
>
>   trying this in two different databases the first database contain table
>   with space in his name (Problem when running query)
>   the second no ( work fine)
>
>   the same problme if you get Oid from schema name.
>   SELECT 'public'::regnamespace::oid;  work ;
>   create schema " this is test" ;
>   SELECT 'this is test'::regnamespace::oid;  not working ;

Try:

SELECT '"this is test"'::regnamespace::oid;  not working ;

Same for table name. As example:

select '"space table"'::regclass;
  regclass
---------------
  "space table"
(1 row)

>
>   i have question how use join between  information_schema.columns and
>   pg_attribute ? thanks
>
> regards*


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

Peter J. Holzer
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 <[hidden email]> 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
| |   | [hidden email]         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

Tom Lane-2
"Peter J. Holzer" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

Tom Lane-2
I wrote:
> ... 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".

Sigh, that should be "between pg_class and pg_attribute" of course.
I'm really only firing on one cylinder today :-(

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

John Mikel
In reply to this post by Adrian Klaver-4

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 will occur
if i run this query in other database will work fine 
I tested this on pg 11.1  , pg 10.3, pg 9.6

PS:Sorry if this message is duplicated i canceled the previous message by mistake by clicking on link



Le jeu. 13 juin 2019 à 17:33, Adrian Klaver <[hidden email]> a écrit :
On 6/13/19 8:14 AM, John Mikel wrote:
> * hi ,
>   i am here to tell you that this  test in query is not working when the
>   table name in the database or schema name   contain space
>   a.table_name::regclass=b.attrelid
>
>   a is information_schema.columns
>   b is pg_attribute
>
>   trying this in two different databases the first database contain table
>   with space in his name (Problem when running query)
>   the second no ( work fine)
>
>   the same problme if you get Oid from schema name.
>   SELECT 'public'::regnamespace::oid;  work ;
>   create schema " this is test" ;
>   SELECT 'this is test'::regnamespace::oid;  not working ;

Try:

SELECT '"this is test"'::regnamespace::oid;  not working ;

Same for table name. As example:

select '"space table"'::regclass;
  regclass
---------------
  "space table"
(1 row)

>
>   i have question how use join between  information_schema.columns and
>   pg_attribute ? thanks
>
> regards*


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: bug regclass::oid

Adrian Klaver-4
On 6/17/19 1:58 AM, 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 will occur
> if i run this query in other database will work fine
> I tested this on pg 11.1  , pg 10.3, pg 9.6
>
> PS:Sorry if this message is duplicated i canceled the previous message
> by mistake by clicking on link
>

The previous message came through, see the replies. The basic issue is
that a table name with a space in it will need to be quoted. So use
quote_ident() per Peter and Tom's suggestions.


--
Adrian Klaver
[hidden email]