[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[LacaK]

Hi John,
> 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.
>
I think, that if this view(s) is(are) presented in all versions of 
PostgreSQL which fcl-db is going to support, that it is no problem use them
(I must note, that my preffered way is follow sql standard 
INFORMATION_SCHEMA views at least in column naming)

> 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.)
Hm, if you want get also schema_name then you should use this approach:
sqlquery1.SchemaType:=stTables;
sqlquery1.Open;
and in loop fill TStrings using 
sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name')

>
> 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.
>
Personally I am not fan of this approach (mainly, because of keeping 
Delphi compatibility).

-Laco.

> 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
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>