Code: Select all
function TZPostgreSQLDatabaseMetadata.GetColumns(const Catalog: string;
const SchemaPattern: string; const TableNamePattern: string;
const ColumnNamePattern: string): IZResultSet;
{const
VARHDRSZ = 4;
}var
TypeOid, AttTypMod: Integer;
Key, SQL, PgType: string;
begin
Key := Format('get-columns:%s:%s:%s:%s',
[Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern]);
Result := GetResultSetFromCache(Key);
if Result = nil then
begin
Result := ConstructVirtualResultSet(TableColColumnsDynArray);
if HaveMinimumServerVersion(7, 3) then
begin
SQL := 'SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,'
+ 'a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description '
+ ' FROM pg_catalog.pg_namespace n '
+ ' JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) '
+ ' JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) '
+ ' LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid'
+ ' AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc'
+ ' ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) '
+ ' LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid'
+ ' AND dc.relname=''pg_class'') LEFT JOIN pg_catalog.pg_namespace dn'
+ ' ON (dc.relnamespace=dn.oid AND dn.nspname=''pg_catalog'') '
+ ' WHERE a.attnum > 0 AND NOT a.attisdropped';
if SchemaPattern <> '' then
begin
SQL := SQL + ' AND n.nspname LIKE '
+ EscapeString(SchemaPattern);
end;
end
else
begin
SQL := 'SELECT NULL::text AS nspname,c.relname,a.attname,a.atttypid,'
+ 'a.attnotnull,a.atttypmod,a.attlen,a.attnum,NULL AS adsrc,'
+ 'NULL AS description FROM pg_class c, pg_attribute a '
+ ' WHERE a.attrelid=c.oid AND a.attnum > 0 ';
end;
SQL := SQL + ' AND c.relname LIKE ' + EscapeString(ToLikeString(TableNamePattern))
+ ' AND a.attname LIKE ' + EscapeString(ToLikeString(ColumnNamePattern))
+ ' ORDER BY nspname,relname,attnum';
with GetConnection.CreateStatement.ExecuteQuery(SQL) do
begin
while Next do
begin
AttTypMod := GetIntByName('atttypmod');
TypeOid := StrToInt(GetStringByName('atttypid'));
PgType := GetPostgreSQLType(TypeOid);
Result.MoveToInsertRow;
Result.UpdateNull(1);
Result.UpdateString(2, GetStringByName('nspname'));
Result.UpdateString(3, GetStringByName('relname'));
Result.UpdateString(4, GetStringByName('attname'));
Result.UpdateInt(5, Ord(GetSQLTypeByOid(TypeOid)));
Result.UpdateString(6, PgType);
Result.UpdateInt(8, 0);
if (PgType = 'bpchar') or (PgType = 'varchar') then
begin
if AttTypMod <> -1 then
Result.UpdateInt(7, AttTypMod - 4)
else Result.UpdateInt(7, 0);
end
else if (PgType = 'numeric') or (PgType = 'decimal') then
begin
AttTypMod := AttTypMod - 4;
Result.UpdateInt(7, AttTypMod shr 16);
Result.UpdateInt(9, AttTypMod and $FFFF);
Result.UpdateInt(10, 10);
end
else if (PgType = 'bit') or (PgType = 'varbit') then
begin
Result.UpdateInt(7, AttTypMod);
Result.UpdateInt(10, 2);
end
else
begin
Result.UpdateInt(7, GetIntByName('attlen'));
Result.UpdateInt(10, 2);
end;
Result.UpdateNull(8);
if GetBooleanByName('attnotnull') then
begin
Result.UpdateString(18, 'NO');
Result.UpdateInt(11, Ord(ntNoNulls));
end
else
begin
Result.UpdateString(18, 'YES');
Result.UpdateInt(11, Ord(ntNullable));
end;
Result.UpdateString(12, GetStringByName('description'));
Result.UpdateString(13, GetStringByName('adsrc'));
Result.UpdateNull(14);
Result.UpdateNull(15);
Result.UpdateInt(16, Result.GetInt(7));
Result.UpdateInt(17, GetIntByName('attnum'));
Result.UpdateNullByName('AUTO_INCREMENT');
Result.UpdateBooleanByName('CASE_SENSITIVE',
GetIdentifierConvertor.IsCaseSensitive(
GetStringByName('attname')));
Result.UpdateBooleanByName('SEARCHABLE', True);
Result.UpdateBooleanByName('WRITABLE', True);
Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
Result.UpdateBooleanByName('READONLY', False);
Result.InsertRow;
end;
Close;
end;
AddResultSetToCache(Key, Result);
end;
end;
Sandeep
