DBX driver for Postgres -> GetIndexNames

dany40 · Post by **dany40** » Tue 30 Jun 2009 22:59

Hi;

I can't get index list for the database tables using TSQL.Connecton.GetIndexNames; it always returns an empty list. For example:

S := TStringList.Create;
Con.GetIndexNames('T1', SL);

I tested to get the list of table names with TSQL.Connecton.GetTableNames and it works fine.

Any help will be appreciated.

Regards,

Dany

Plash · Post by **Plash** » Wed 01 Jul 2009 07:44

We cannot reproduce the problem.
Please specify your Delphi version.

dany40 · Post by **dany40** » Wed 01 Jul 2009 10:42

Thank you. I use Delphi 2007 - DBX4.
Can you give me an example that works?

dany40 · Post by **dany40** » Wed 01 Jul 2009 11:23

Let me add, GetIndexNames is working fine if I run the same program using Devart's DBX driver for Firebird. But the I can't find how to make it working for Postgres.

My tablenames are all uppercase, maybe is something about that?
All the tables are in the schema called "public".

dany40 · Post by **dany40** » Wed 01 Jul 2009 12:37

I continued more tests with DBX4/Delphi 2007 and Devarts driver for Firebird, and I found that GetIndexNames is not returning all the indexes for each table.

So, Firebird driver is returning "some" indexes, and Postgres driver is returning "no one" indexes.

Dany

Plash · Post by **Plash** » Thu 02 Jul 2009 08:39

Metadata for tables with uppercase names is not supported. TableName parameter is treated as case-insensitive. So the PostgreSQL driver always search for a table with lowercase name.

dany40 · Post by **dany40** » Thu 02 Jul 2009 12:43

Devart's driver for Firebird and Postgress are not supporting uppercased objects?. What is the solution?, ... becasuse I can't change my customers databases.

Plash · Post by **Plash** » Fri 03 Jul 2009 09:21

GetIndexNames does not support lowercase table names for the Firebird driver and uppercase table names for the PostgreSQL driver.

The following query is used in PostgreSQL driver to get indexes. You can execute it yourself.

Code: Select all

SELECT
  n.nspname AS TABLE_SCHEMA, t.relname AS TABLE_NAME,
  n.nspname AS INDEX_SCHEMA, c.relname AS INDEX_NAME,
  a.attname AS COLUMN_NAME, a.attnum AS COLUMN_POSITION,
  i.indisunique AS UNIQUE
FROM pg_index i
  INNER JOIN pg_class c ON c.oid = i.indexrelid
  INNER JOIN pg_class t ON t.oid = i.indrelid
  INNER JOIN pg_namespace n ON n.oid = t.relnamespace
  INNER JOIN pg_attribute a ON a.attrelid = i.indrelid and a.attnum = any (i.indkey)
WHERE
  n.nspname = 'public' AND t.relname = 'T1'
ORDER BY n.nspname, t.relname, c.relname, a.attnum

dany40 · Post by **dany40** » Fri 03 Jul 2009 23:18

OK, thank you, I will give it a try. BTW, on the Firebird database the tables are all uppercase, but GetIndexNames is not returning me all the indexnames; it is returning only some.

Plash · Post by **Plash** » Mon 06 Jul 2009 13:06

We could not reproduce the problem with the Firebird driver. Please send to dbx*devart*com a complete small sample that demonstrates the problem, including the script for creating database objects.