TZSQLMetadata is not returning the list of tables for PostgreSQL. The problem is in the following method. Here is my fix for this. I have tested it and it is working ok for me.
[syntax="delphi"]function TZPostgreSQLDatabaseMetadata.GetTables(const Catalog: string;
const SchemaPattern: string; const TableNamePattern: string;
const Types: TStringDynArray): IZResultSet;
var
I: Integer;
Key: string;
TableType, OrderBy, SQL: string;
UseSchemas: Boolean;
LTypes: TStringDynArray;
begin
Key := GetTablesMetaDataCacheKey(Catalog,SchemaPattern,TableNamePattern,Types);
Result := GetResultSetFromCache(Key);
if Result = nil then
begin
UseSchemas := True;
if HaveMinimumServerVersion(7, 3) then
begin
SQL := ' SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,'
+ ' c.relname AS TABLE_NAME, '
+ ' CASE (n.nspname LIKE ''pg\\_%'')'
+ ' OR (n.nspname=''information_schema'')'
+ ' WHEN true THEN CASE n.nspname '
+ ' WHEN ''pg_catalog'' THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''SYSTEM TABLE'''
+ ' WHEN ''v'' THEN ''SYSTEM VIEW'' '
+ ' WHEN ''i'' THEN ''SYSTEM INDEX'' '
+ ' ELSE NULL '
+ ' END '
+ ' WHEN ''information_schema'' THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''SYSTEM TABLE'''
+ ' WHEN ''v'' THEN ''SYSTEM VIEW'' '
+ ' WHEN ''i'' THEN ''SYSTEM INDEX'' '
+ ' ELSE NULL '
+ ' END '
+ ' WHEN ''pg_toast'' THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''SYSTEM TOAST TABLE'' '
+ ' WHEN ''i'' THEN ''SYSTEM TOAST INDEX'' '
+ ' ELSE NULL '
+ ' END '
+ ' ELSE CASE c.relkind '
+ ' WHEN ''r'' THEN ''TEMPORARY TABLE'' '
+ ' WHEN ''i'' THEN ''TEMPORARY INDEX'' '
+ ' ELSE NULL '
+ ' END '
+ ' END '
+ ' WHEN false THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''TABLE'' '
+ ' WHEN ''i'' THEN ''INDEX'' '
+ ' WHEN ''S'' THEN ''SEQUENCE'' '
+ ' WHEN ''v'' THEN ''VIEW'' '
+ ' ELSE NULL '
+ ' END '
+ ' ELSE NULL '
+ ' END '
+ ' AS TABLE_TYPE, d.description AS REMARKS '
+ ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c '
+ ' LEFT JOIN pg_catalog.pg_description d'
+ ' ON (c.oid = d.objoid AND d.objsubid = 0) '
+ ' LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid'
+ ' AND dc.relname=''pg_class'') LEFT JOIN pg_catalog.pg_namespace dn'
+ ' ON (dn.oid=dc.relnamespace AND dn.nspname=''pg_catalog'') '
+ ' WHERE c.relnamespace = n.oid ';
if SchemaPattern <> '' then // cannot happen due to SchemaPattern := '%'
begin
SQL := SQL + ' AND n.nspname LIKE '''
+ EscapeQuotes(ToLikeString(SchemaPattern)) + ''' ';
end;
OrderBy := ' ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ';
end
else
begin
UseSchemas := False;
TableType := ' CASE c.relname LIKE ''pg\\_%'' '
+ 'WHEN true THEN CASE c.relname LIKE ''pg\\_toast\\_%'' '
+ 'WHEN true THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''SYSTEM TOAST TABLE'' '
+ ' WHEN ''i'' THEN ''SYSTEM TOAST INDEX'' '
+ ' ELSE NULL '
+ 'END '
+ 'WHEN false THEN CASE c.relname LIKE ''pg\\_temp\\_%'' '
+ ' WHEN true THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''TEMPORARY TABLE'' '
+ ' WHEN ''i'' THEN ''TEMPORARY INDEX'' '
+ ' ELSE NULL '
+ ' END '
+ ' WHEN false THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''SYSTEM TABLE'' '
+ ' WHEN ''v'' THEN ''SYSTEM VIEW'' '
+ ' WHEN ''i'' THEN ''SYSTEM INDEX'' '
+ ' ELSE NULL '
+ ' END '
+ ' ELSE NULL '
+ 'END '
+ 'ELSE NULL '
+ 'END '
+ 'WHEN false THEN CASE c.relkind '
+ ' WHEN ''r'' THEN ''TABLE'' '
+ ' WHEN ''i'' THEN ''INDEX'' '
+ ' WHEN ''S'' THEN ''SEQUENCE'' '
+ ' WHEN ''v'' THEN ''VIEW'' '
+ ' ELSE NULL '
+ 'END '
+ 'ELSE NULL '
+ ' END ';
OrderBy := ' ORDER BY TABLE_TYPE,TABLE_NAME ';
SQL := 'SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM,'
+ ' c.relname AS TABLE_NAME, ' + TableType + ' AS TABLE_TYPE,'
+ ' NULL AS REMARKS FROM pg_class c WHERE true ';
end;
if (Types = nil) or (High(Types) = 0) then
begin
SetLength(LTypes, 5);
LTypes[0] := 'TABLE';
LTypes[1] := 'VIEW';
LTypes[2] := 'INDEX';
LTypes[3] := 'SEQUENCE';
LTypes[4] := 'TEMPORARY TABLE';
end
else
LTypes := Types;
SQL := SQL + ' AND c.relname LIKE ''' + EscapeQuotes(ToLikeString(TableNamePattern))
+ ''' ';
//SC 21/10/2006
if (Length(Types) > 0) then
SQL := SQL + 'AND (false ';
//SC 21/10/2006
for I := 0 to High(Types) do
SQL := SQL + ' OR ( ' + TableTypeSQLExpression(LTypes, UseSchemas) + ' ) ';
//SC 21/10/2006
if (Length(Types) > 0) then
SQL := SQL + ' )';
//SC 21/10/2006
SQL := SQL + OrderBy;
Result := CopyToVirtualResultSet(
GetConnection.CreateStatement.ExecuteQuery(SQL),
ConstructVirtualResultSet(TableColumnsDynArray));
AddResultSetToCache(Key, Result);
end;
end;[/syntax]
Regards
Sandeep
[edit]Sourcecode formatted to make it readable
[/edit]
