Zeoslib Portal

Code: Select all

function TZPostgreSQLDatabaseMetadata.UncachedGetColumns(const Catalog: string;
  const SchemaPattern: string; const TableNamePattern: string;
  const ColumnNamePattern: string): IZResultSet;
{const
  VARHDRSZ = 4;
}var
  TypeOid, AttTypMod: Integer;
  SQL, PgType: string;
begin
    Result:=inherited UncachedGetColumns(Catalog, SchemaPattern, TableNamePattern, ColumnNamePattern);

    if (GetDatabaseInfo as IZPostgreDBInfo).HasMinimumServerVersion(7, 3) then
    begin
      SQL := 'SELECT n.nspname,' {1}
        + 'c.relname,' {2}
        + 'a.attname,' {3}
        + 'a.atttypid,' {4}
        + 'a.attnotnull,' {5}
        + 'a.atttypmod,' {6}
        + 'a.attlen,' {7}
        + 'a.attnum,' {8}
        + 'pg_get_expr(def.adbin, def.adrelid) as adsrc,' {9}
        + 'dsc.description ' {10}
        + ' 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 else begin
         SQL := SQL + ' AND pg_table_is_visible (c.oid) ';
      end; 
    end
    else
    begin
      SQL := 'SELECT NULL::text AS nspname,' {1}
        + 'c.relname,' {2}
        + 'a.attname,' {3}
        + 'a.atttypid,' {4}
        + 'a.attnotnull,' {5}
        + 'a.atttypmod,' {6}
        + 'a.attlen,' {7}
        + 'a.attnum,' {8}
        + 'NULL AS adsrc,' {9}
        + 'NULL AS description' {10}
        + '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 := GetInt(6 {atttypmod});

        TypeOid := GetInt(4 {atttypid});
        PgType := GetPostgreSQLType(TypeOid);

        Result.MoveToInsertRow;
        Result.UpdateNull(1);
        Result.UpdateString(2, GetString(1 {nspname}));
        Result.UpdateString(3, GetString(2 {relname}));
        Result.UpdateString(4, GetString(3 {attname})); 
        Result.UpdateInt(5, Ord(GetSQLTypeByOid(TypeOid)));
        Result.UpdateString(6, PgType);
        Result.UpdateInt(8, 0);

        if (PgType = 'bpchar') or (PgType = 'varchar') or (PgType = 'enum') 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
          Result.UpdateInt(7, ((AttTypMod - 4) div 65536)); //precision
          Result.UpdateInt(9, ((AttTypMod -4) mod 65536)); //scale
          Result.UpdateInt(10, 10); //base? ten as default
        end
        else if (PgType = 'bit') or (PgType = 'varbit') then
        begin
          Result.UpdateInt(7, AttTypMod);
          Result.UpdateInt(10, 2);
        end
        else
        begin
          Result.UpdateInt(7, GetInt(7 {attlen}));
          Result.UpdateInt(10, 2);
        end;

        Result.UpdateNull(8);
        if GetBoolean(5 {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, GetString(10 {description}));
        Result.UpdateString(13, GetString(9 {adsrc}));
        Result.UpdateNull(14);
        Result.UpdateNull(15);
        Result.UpdateInt(16, Result.GetInt(7));
        Result.UpdateInt(17, GetInt(8 {attnum}));

        Result.UpdateNullByName('AUTO_INCREMENT');
        Result.UpdateBooleanByName('CASE_SENSITIVE',
          GetIdentifierConvertor.IsCaseSensitive(GetString(3 {attname})));
        Result.UpdateBooleanByName('SEARCHABLE', True);
        Result.UpdateBooleanByName('WRITABLE', True);
        Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
        Result.UpdateBooleanByName('READONLY', False);

        Result.InsertRow;
      end;
      Close;
    end;
end;

Code: Select all

TZConnection.ClientCodepage := 'utf8';
TZConnection.ControlsCodepage := cGet_ACP; //if you're a Ansi-Delphi user (<=D2007)
TZConnection.AutoEncodeStrings := True;