[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[John]

Hi,

Short version: A call to TSQLConnection.GetTableNames returns a list of 
tables on all schemas/owners, without the owner.  Unless you are using a 
simple database with only one owner and are logging in as the owner, 
this is fairly unhelpful.

Environment:  FPC Fixes 2.6 , Lazarus trunk, both fairly recent.  Linux 
(Fedora 14)(not recent).  Postgresql server 8.4 (also not recent!)

Analysis:
As far as I can work out, a call to GetTableNames calls GetDBInfo, with 
parameters
   ASchemaType : TSchemaType - This specifies what info we want - user 
tables, sys tables, procedures, columns etc
   ASchemaObjectName - Doesn't seem to be used, it is specified as ""
   AReturnField : string; - the name of the field to be returned
   AList: TStrings - this is what the list of tables is returned in.


ASchemaType, ASchemaObjectName and AReturnField are 'passed' as 
properties, and a sqlquery is opened with no query specified.  The 
ASchemaType causes the TSQLQuery.prepare statement to fetch the actual 
sql used from theconnection.GetSchemaInfoSQL, where the query sql is 
overridden for each species of connection.  In the case of postgres - 
pqconnection - the sql is:

select relfilenode as recno, <DatabaseName> as catalog_name, '' as 
schema_name, relname as table_name, 0 as table_type
from pg_class
where (relowner > 1) and relkind=''r''
order by relname;

Thus schema name is not selected for postresql, nor is it returned for 
any connection. as only the field specified by AReturnField, in this 
case table_name, is added to the output list.  Note that pg_class does 
not have an easy access to the schema/owner by name, it would require a 
join on an oid.

Suggestions:
1)  The pg database also has a view, pg_stat_user_tables, that is simple 
and has the schema as well as the table name. There may be some good 
reason for not using it that I am not aware of.

2)  The simple way to get the schemas would be to simply 'select ... 
schemaname||'.'||relname as table_name ... from pg_stat_user_tables.  
This would be a change ONLY to the sql in pqconnection.  (If worst comes 
to worst, I dare say could create my own pqconnection derivative with 
this change.)

3)  A better solution, in my opinion, would be to add some extra 
TSchemaTypes,  with matching TSQLConnection calls, for GetSchemas, 
GetTablesInSchema, and possibly the "TableBySchema" option as in (2).  
It seems the "infrastructure" already exists to do all of these things.

If this concept is acceptable I would be happy to work on patches for 
TSQLConnection and TPQConnection, but my knowledge of the other dbs 
varies from limited through out-of-date (Oracle) to none.

cheers
John Sunderland