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

2009/11/12 Jitendra Lenka <jitendra.lenka@csqldb.com>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05180
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Jitendra Lenka
> Email address: =C2=A0 =C2=A0 =C2=A0jitendra.lenka@csqldb.com
> PostgreSQL version: 8.3.3
> Operating system: =C2=A0 Fedora10
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0How to get only User created tabl=
es by using SQLTables()
> in ODBC
> Details:
>
> Hi,
>
> I created a table named "t1" in postgres. Used SQLTables() function in OD=
BC
> 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=3D# \dt
No relations found.
postgres=3D# create table omega(a integer);
CREATE TABLE
postgres=3D# \x

postgres=3D# 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=3DSQLTables(hstmt, NULL, 0, NULL, 0, NULL, 0, (SQLCHAR*) "TABLE",
> SQL_NTS);
>
> while (SQL_SUCCEEDED(retValue =3D SQLFetch(hstmt))) {
> =C2=A0 =C2=A0 =C2=A0 =C2=A0SQLUSMALLINT i;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0i=3D3;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0if(i<=3Dcolumns){
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SQLINTEGER indicator;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0char buf[512];
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0retValue=3DSQLGetData(hstmt, i, =
SQL_C_CHAR,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 buf, sizeof(buf), &indicator);
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (SQL_SUCCEEDED(retValue)) {
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 if (indicator =3D=
=3D SQL_NULL_DATA) strcpy(buf, "NULL");
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 else{
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 printf("table:%s\n",buf);
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 }
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0}
> =C2=A0 =C2=A0 =C2=A0}
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>