Re: BUG #5180: How to get only User created tables by using SQLTables() in ODBC

2009/11/12 Jitendra Lenka <jitendra(dot)lenka(at)csqldb(dot)com>:
>
> The following bug has been logged online:
>
> Bug reference:      5180
> Logged by:          Jitendra Lenka
> Email address:      jitendra(dot)lenka(at)csqldb(dot)com
> PostgreSQL version: 8.3.3
> Operating system:   Fedora10
> Description:        How to get only User created tables by using SQLTables()
> in ODBC
> Details:
>
> Hi,
>
> I created a table named "t1" in postgres. Used SQLTables() function in ODBC
> to retrieve the table "t1" only. But it retrieves many System and Metadata
> tables which bothers me.
>
> Follow below to get all system tables along with user created table "t1".
>
> sql_features
> sql_implementation_info
> sql_languages
> sql_packages
> sql_parts
> sql_sizing
> sql_sizing_profiles
> t1
>
> Can I get only user created table ?

Hello,

look on information_schema, please.

postgres=# \dt
No relations found.
postgres=# create table omega(a integer);
CREATE TABLE
postgres=# \x

postgres=# SELECT *
FROM information_schema.tables
WHERE table_schema NOT IN
('pg_catalog','information_schema');
-[ RECORD 1 ]----------------+-----------
table_catalog | postgres
table_schema | public
table_name | omega
table_type | BASE TABLE
self_referencing_column_name |
reference_generation |
user_defined_type_catalog |
user_defined_type_schema |
user_defined_type_name |
is_insertable_into | YES
is_typed | NO
commit_action

Regards
Pavel Stehule

>
> If anybody find it good please report it in my mail ID.
>
> Your reply is highly solicited.
>
> Please find the code:
> --------------------
> retValue=SQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*) "TABLE",
> SQL_NTS);
>
> while (SQL_SUCCEEDED(retValue = SQLFetch(hstmt))) {
>        SQLUSMALLINT i;
>        i=3;
>        if(i<=columns){
>            SQLINTEGER indicator;
>            char buf[512];
>            retValue=SQLGetData(hstmt, i, SQL_C_CHAR,
>                         buf, sizeof(buf), &indicator);
>            if (SQL_SUCCEEDED(retValue)) {
>                 if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
>                 else{
>                         printf("table:%s\n",buf);
>
>                  }
>             }
>          }
>      }
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>