Thread: Getting the table ID
Hi, guys, In the database theory each table is identified as "schema_name"."table_name". When I tried to look at how to get the table id inside the PostgreSQL, I saw that I needed to look at the pg_class table. SELECT oid FROM pg_class WHERE relname = "table_name"; However that query will give a non-unique table id (see the first sentence). So how do I get the table id based on the "schema_name.table_name"? There is a pg_namespace table - is this where the schema should come from? If yes - how? Looking at that table I don't see any unique fields... Or is this something that is hidden? In fact I'm trying to run following query: SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid = c.relnamespace AND c.relname = ? AND nc.nspname = ?; from my ODBC based program, but it returns 0 rows on SQLFetch. I know PostgreSQL does not use '?' for query parameters but I thought that since its an ODBC everything should work. Nevertheless, all bindings were successful, but now rows are returned. Is this query correct? Thank you.
On Mon, Jul 18, 2022 at 8:11 PM Igor Korot <ikorot01@gmail.com> wrote:
There is a pg_namespace table - is this where the schema should come from?
If yes - how?
Looking at that table I don't see any unique fields...
Or is this something that is hidden?
Catalogs don't have all of the same metadata that user-space tables have.
"namespace" is the historical and internally used label for what we present to users as "schema"
In fact I'm trying to run following query:
SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;
Then either relname or nspname caused the records to filter out because the join part of that where clause is correct.
I suggest you experiment using psql and literals so you can provide both actual queries and results more easily. The whole self-contained script thing is very helpful.
You can also learn quite a bit by echoing queries in psql then using the various description metacommands that query these same catalogs - thus echoing the queries psql itself uses to answer this same question.
David J.
On 19 Jul 2022, at 5:10, Igor Korot wrote:
Hi, guys,
In the database theory each table is identified as "schema_name"."table_name".
When I tried to look at how to get the table id inside the PostgreSQL,
I saw that I needed to look at the pg_class table.
SELECT oid FROM pg_class WHERE relname = "table_name";
However that query will give a non-unique table id (see the first sentence).
So how do I get the table id based on the "schema_name.table_name"?
There is a pg_namespace table - is this where the schema should come from?
If yes - how?
Looking at that table I don't see any unique fields...
Or is this something that is hidden?
In fact I'm trying to run following query:
SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;
from my ODBC based program, but it returns 0 rows on SQLFetch.
I know PostgreSQL does not use '?' for query parameters
but I thought that since its an ODBC everything should work.
Nevertheless, all bindings were successful, but now rows are returned.
Is this query correct?
Thank you.
That's more or less the same query that I am using:
select
r.oid as oid,
n.nspname || '.' || r.relname as name
from
pg_catalog.pg_namespace n
join
pg_catalog.pg_class r on n.oid = r.relnamespace
where
(r.relkind = 'r') and
(n.nspname not like 'pg_%') and
(n.nspname != 'information_schema') and
(n.nspname = 'email') and
(r.relname = 'emailhistory')
Maybe your problem has to to with uppercase/lowercase schema and/or table names?
Servus,
Walter
Hi, guys,
On Tue, Jul 19, 2022 at 4:42 AM Walter Dörwald <walter@livinglogic.de> wrote:
>
> On 19 Jul 2022, at 5:10, Igor Korot wrote:
>
> Hi, guys,
>
> In the database theory each table is identified as "schema_name"."table_name".
>
> When I tried to look at how to get the table id inside the PostgreSQL,
> I saw that I needed to look at the pg_class table.
>
> SELECT oid FROM pg_class WHERE relname = "table_name";
>
> However that query will give a non-unique table id (see the first sentence).
>
> So how do I get the table id based on the "schema_name.table_name"?
>
> There is a pg_namespace table - is this where the schema should come from?
> If yes - how?
> Looking at that table I don't see any unique fields...
> Or is this something that is hidden?
>
> In fact I'm trying to run following query:
>
> SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
> c.relnamespace AND c.relname = ? AND nc.nspname = ?;
>
> from my ODBC based program, but it returns 0 rows on SQLFetch.
>
> I know PostgreSQL does not use '?' for query parameters
> but I thought that since its an ODBC everything should work.
>
> Nevertheless, all bindings were successful, but now rows are returned.
>
> Is this query correct?
>
> Thank you.
>
> That's more or less the same query that I am using:
>
> select
> r.oid as oid,
> n.nspname || '.' || r.relname as name
> from
> pg_catalog.pg_namespace n
> join
> pg_catalog.pg_class r on n.oid = r.relnamespace
> where
> (r.relkind = 'r') and
> (n.nspname not like 'pg_%') and
> (n.nspname != 'information_schema') and
> (n.nspname = 'email') and
> (r.relname = 'emailhistory')
>
> Maybe your problem has to to with uppercase/lowercase schema and/or table names?
Below is my C++ code based on the ODBC library:
[code]
SQLHSTMT stmt = 0;
SQLHDBC hdbc;
SQLLEN cbName, cbTableName = SQL_NTS, cbSchemaName = SQL_NTS;
long id;
int result = 0;
std::wstring query;
SQLWCHAR *qry = NULL, *tname = NULL, *sname = NULL;;
query = L"SELECT c.oid FROM pg_class c, pg_namespace nc WHERE nc.oid =
c.relnamespace AND c.relname = ? AND nc.nspname = ?;";
qry = new SQLWCHAR[query.length() + 2];
tname = new SQLWCHAR[tableName.length() + 2];
sname = new SQLWCHAR[schemaName.length() + 2];
memset( tname, '\0', tableName.length() + 2 );
memset( sname, '\0', schemaName.length() + 2);
uc_to_str_cpy( sname, schemaName );
uc_to_str_cpy( tname, tableName );
memset( qry, '\0', query.length() + 2 );
uc_to_str_cpy( qry, query );
SQLRETURN retcode = SQLAllocHandle( SQL_HANDLE_DBC, m_env, &hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 0 );
result = 1;
}
else
{
SQLSMALLINT OutConnStrLen;
retcode = SQLDriverConnect( hdbc, NULL, m_connectString, SQL_NTS,
NULL, 0, &OutConnStrLen, SQL_DRIVER_NOPROMPT );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
auto dbName = new SQLWCHAR[pimpl->m_dbName.length() + 2];
memset( dbName, '\0', pimpl->m_dbName.length() + 2 );
uc_to_str_cpy( dbName, pimpl->m_dbName );
retcode = SQLSetConnectAttr( hdbc, SQL_ATTR_CURRENT_CATALOG, dbName, SQL_NTS );
delete[] dbName;
dbName = nullptr;
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2 );
result = 1;
retcode = SQLEndTran( SQL_HANDLE_DBC, m_hdbc, SQL_ROLLBACK );
}
else
{
retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
retcode = SQLPrepare( stmt, qry, SQL_NTS );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
SQLSMALLINT dataType[2], decimalDigit[2], nullable[2];
SQLULEN parameterSize[2];
retcode = SQLDescribeParam( stmt, 1, &dataType[0], ¶meterSize[0],
&decimalDigit[0], &nullable[0] );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT,
dataType[0], parameterSize[0], decimalDigit[0], tname, 0, &cbTableName
);
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
}
if( !result )
{
retcode = SQLDescribeParam( stmt, 2, &dataType[1], ¶meterSize[1],
&decimalDigit[1], &nullable[1] );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindParameter( stmt, 2, SQL_PARAM_INPUT, SQL_C_DEFAULT,
dataType[1], parameterSize[1], decimalDigit[1], sname, 0,
&cbSchemaName );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
}
}
if( !result )
{
retcode = SQLExecute( stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLBindCol( stmt, 1, SQL_C_SLONG, &id, 100, &cbName );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
retcode = SQLFetch( stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO &&
retcode != SQL_NO_DATA )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else if( retcode == SQL_NO_DATA )
tableId = 0;
}
}
}
}
}
}
}
}
delete[] qry;
qry = NULL;
delete[] tname;
tname = NULL;
delete[] sname;
sname = NULL;
if( stmt )
{
retcode = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 1, stmt );
result = 1;
}
else
{
stmt = 0;
retcode = SQLDisconnect( hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
{
retcode = SQLFreeHandle( SQL_HANDLE_DBC, hdbc );
if( retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
{
GetErrorMessage( errorMsg, 2, hdbc );
result = 1;
}
else
hdbc = 0;
}
}
}
return result;
[/code]
Every single SQLXXX() call is successful (returns 0 - SQL_SUCCESS),
except SQLFetch(),
which returns 100 (SQL_NO_DATA).
Can you spot an error?
Thank you.
>
> Servus,
> Walter