Postgres Pro
Downloads
Home   >   mailing lists

Re: bug regclass::oid - Mailing list pgsql-general

From John Mikel
Subject Re: bug regclass::oid
Date
Msg-id CAAC-7YAOKcBmiNQFjskK7ywFEJ2rRzPQZy2gYcWGg+roHHzo7A@mail.gmail.com
Whole thread Raw
In response to Re: bug regclass::oid  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: bug regclass::oid
List pgsql-general
Tree view

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 <adrian.klaver@aklaver.com> 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
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: Rob Nikander
Date:
Subject: Async client libraries - not worth it?
Next
From: Dave Cramer
Date:
Subject: Re: Async client libraries - not worth it?