[fpc-pascal] GetTableNames in TSQLConnection / Postgresql

[michael.vancanneyt at wisa.be]

On Mon, 17 Dec 2012, LacaK wrote:

>>>> 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') 
>> At present, the schema isn't read into the query (so we have to change the 
>> db connection), and the above code would have to be put into TSQLConnecion, 
>> where (1) it would only apply to stTables, and (2) would not work if any 
>> database species did not return the schema.  Probably to do this it would 
>> be necessary to be able to select multiple fields in the AReturnField of 
>> GetDBInfo, all of which would be far more complicated than what I suggested 
>> as a "simple" solution!
> *Yes it will be more complicated.
>
> I did short comparasion in Delphi and in case of BDE table names are returned 
> as "schema.table" in case of ADO,DBX only "table" without schema is returned. 
> So again inconsistency between various DB client technologies.

IMHO all the more reason to use 2 different fields, and keep table in 1 field.

> If others agree, IMO we can do it like this:
> 1. use PG specific system view query, where we add SCHEMA_TABLE_NAME column 
> (other columns like SCHEMA_NAME, TABLE_NAME, TABLE_TYPE etc remains) which 
> will be as you suggested: schemaname||'.'||relname

I would use 2 separate fields. This way the table field always only contains
the table. People interested in the schema, can read the schema field.

It's easier to concatenate 2 fields than to separate out 2 parts of a field,
doubly so if you don't know whether the 2 parts are there or not.

Michael.