dotConnect for PostgreSQL Documentation
In This Topic
    Retrieving Metadata
    In This Topic
    This feature is not supported for applications, targeting .NET Core 1 or .NET Standard 1.3. It is supported only for full .NET Framework, .NET Core 2.0 and higher, or .NET Standard 2.0 or higher.

    Usually you have to dig through SQL references to find out how to get metadata information for specific DBMS. There are few servers that support same SQL commands. Sometimes the syntax differs slightly, sometimes a server does not support certain statement. Now you can forget about those problems because dotConnect for PostgreSQL retrieves the metadata for you.

    Since dotConnect for PostgreSQL 2.00 you can take advantage of a very useful feature - GetSchema method. It allows you to read server schema information without writing queries and parsing the output. All information you may want to obtain is brought to you by single function in easy-to-process format. You can get information on databases, tables, columns, indexes, users, stored procedures and functions, user-defined procedures, and reserved words. The method is introduced in System.Data.Common.DbConnection.

    How To Use

    GetSchema method is available in three overloads, each of them serves its own purpose. All overloads return System.Data.DataTable object that contains information about server elements.

    public virtual abstract DataTable GetSchema();
    
    
    Overloads Public Overridable MustOverride Function GetSchema() As DataTable
    
    

    If you call the GetSchema method without parameters, or with single parameter "MetaDataCollections" (which is actually the same), the table object returned by the method will contain three columns. The first field of every row is a keyword allowed to be passed to the method (as collectionName argument). The second field is the number of restriction values for this keywords (passed through restrictionValues argument). The third field is not used in dotConnect for PostgreSQL. It is always zero.

    public virtual abstract DataTable GetSchema(
       string collectionName
    );
    
    
    Overloads Public Overridable MustOverride Function GetSchema( _
       ByVal collectionName As String _
    ) As DataTable
    
    

    GetSchema with 1 argument returns general information about the collection queried. For example, GetSchema("Users") returns list of users on the server.

    public virtual abstract DataTable GetSchema(
       string collectionName,
       string[] restrictionValues
    );
    
    
    Overloads Public Overridable MustOverride Function GetSchema( _
       ByVal collectionName As String, _
       ByVal restrictionValues() As String _
    ) As DataTable
    
    

    In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.

    Instead of specifying the metadata collection name as a string constant, you may use members of System.Data.DbMetaDataCollectionNames and Devart.Data.PostgreSql.PgSqlMetadataCollectionNames as the first GetSchema argument values. The members of these classes are the string fields, each field stores the corresponding metadata collection name. It is recommended to use these fields rather than manually input the collection names manually as the string constants because in case of using these fields, you will find misspellings at compile-time, and intellisense will show you all the available metadata collection names.

    GetSchema Method Reference

    GetSchema supports two kinds of wildcards: "%" and "_". These special characters behave exactly as in LIKE operator. "%" means zero or more of any characters, "_" means any single character.

    When calling the GetSchema method, you can pass all or few of restrictions, applicable to the chosen collection. In the latter case, you have to put "%" wildcard in the place of omitted restriction, if it must be specified before the restriction you want to apply. For example, GetSchema("Casts", new string[] { "%", "%", "date" }) returns list of all casts to type "date". Or, suppose you want to query all functions within a certain schema: GetSchema("Functions", new string[] { "%", "public" }) returns list of all functions in schema public. Note that this collection may accept 8 restrictions, however, you do not need to specify all of them.

    The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them. Some collections may be not supported in older server versions. If you try to get metadata for unsupported collection you will get exception with message "Collection not defined".

    Collection Name

    Number of restrictions

    Remarks

    MetaDataCollections 0

    Returns this list. Same as using GetSchema() method without parameters.

    ReservedWords 0

    Lists all reserved words used in the server.

    AggregateFunctions 2

    GetSchema("AggregateFunctions") returns detailed list of all aggregate functions on the server.

    • The first restriction for this collection is a name of a function. If specified, the method returns all overloads with given name.
    • The second restriction is name of function definer. For example, you can issue "%" as the first restriction, and "postgres" as the second one, to obtain list of functions defined by user "postgres".
    Arguments 5

    GetSchema("Arguments") returns the list of the function/procedure arguments. The following restrictions may be specified:

    • Schema name
    • Function name
    • User name
    • Type
    • Function OID (for overloaded functions)
    Casts 3

    GetSchema("Casts") returns the list of casts available on the server.

    • The first restriction is source type for the cast.
    • The second restriction is target type for the cast.
    • The third restriction for this collection is the namespace of a cast.
    CompositeTypeAttributes 4

    GetSchema("CompositeTypeAttributes") returns the list of complex type attributes available on the server.

    • The first restriction for this collection is composite type name.
    • The second restriction is schema name.
    • The third restriction is composite type OID.
    • The fourth restriction is attribute name.
    Columns 3

    Returns list of columns, their schema, table, type, and some extra information.

    • Restricted by schema name, GetSchema returns columns of all tables within the schema.
    • The second restriction is name of a table that GetSchema method should search in.
    • At last, you can set column name pattern to filter columns by name.
    Databases 3

    Returns list of databases on the server.

    • The first restriction is definer's name.
    • The second restriction is encoding of the database.
    • The last restriction is a name of a database.
    DatasourceInformation 1

    Returns information about data sources.

    The only restriction is user name.

    Domains 9

    Returns list of domains on the server. The restrictions for this collection are the following:

    • Schema name
    • Domain name
    • Definer
    • Base type OID
    • Base type
    • Type length
    • Type mode
    • Number of dimensions
    • OID
    ForeignKeys 3

    Returns list of foreign key columns on the server. The restrictions for this collection are the following:

    • Schema name
    • Table name
    • Key name
    ForeignKeyColumns 4

    Returns list of foreign keys on the server. The restrictions for this collection are the following:

    • Key name
    • Schema name
    • Table name
    • OID
    Functions 8

    Returns list of functions on the server. The following restrictions may be specified:

    • Schema name
    • Function name
    • Definer
    • Language
    • Arguments count
    • Return type OID
    • Return type
    • OID
    Groups 1

    Lists all groups on the server, including IDs and members.

    The only restriction is a group name.

    Indexes 4

    Returns list of indexes and their details. Restricted by:

    • Schema name
    • Table name
    • Index name
    • OID
    IndexColumns 3

    Returns list of index columns and their details. Restricted by:

    • Schema name
    • Table name
    • Index name
    Languages 2

    Lists all languages on the server.

    The first restriction is a language name, the second is OID.

    NestedTables 6

    Lists table inheritance relationships on the server. Restricted by:

    • Child table name
    • Child schema name
    • Parent table name
    • Parent schema name
    • The 1-based position of parent table columns in the child column list in relation to other parent tables (in case of multiple inheritance - from multiple parents)
    • OID of the child table
    Operators 2

    Returns list of operators allowed on the server.

    Restrict it by a name (the first restriction) and OID (the second restriction).

    OperatorClasses 6

    Lists operator classes on the server. Restricted by:

    • Schema name
    • Definer
    • Indexed data type, for which this operator class can be used
    • Parent schema name
    • Type of values, actually stored in the index, or 0 if it is the same, as indexed data type.
    • OID
    PrimaryKeys 4

    Returns list of primary keys on the server.

    • The first restriction is key name.
    • The second is schema name
    • The third is table name
    • The fourth one is OID
    Procedures 6

    Returns list of functions on the server. The following restrictions may be specified:

    • Schema name
    • Function name
    • Definer
    • Language
    • Arguments count
    • OID
    Rules 4

    Returns list of rules on the server.

    • The first restriction is rule name.
    • The second is table name
    • The third is schema name
    • The fourth one is OID
    Schemas 2

    Returns list of schemas on the server.

    • The first restriction is schema name
    • The second restriction is user name
    Sequences 4

    Returns list of sequences on the server.

    • The first restriction is schema name
    • The second restriction is sequence name
    • The third restriction is user name
    • The fourth restriction is oid
    Tables 3

    Returns list of tables on the server. Restrict the collection with:

    • Schema name
    • Table name
    • User name
    Triggers 6

    Returns list of trigger functions on the server. Restricted by:

    • OID
    • Trigger function name
    • Schema name
    • Definer
    • Language
    • Arguments count
    Types 5

    Returns list of types on the server. Restricted by:

    • Schema name
    • Type name
    • Definer
    • Type of a type. Can have the following values:
      • 'b' - for base types
      • 'c' - for composite types
      • 'd' - for domains
      • 'e' - for enums
      • 'p' - for pseudotypes
      • 'r' - for ranges
    • OID
    UniqueKeys 4

    Returns list of unique keys on the server.

    • The first restriction is key name.
    • The second is schema name
    • The third is table name
    • The fourth one is OID
    UserPrivileges 1

    Returns information about privileges of users on the server.

    When restricted by username, returns information about specific user.

    Users 1

    Lists all users on the server.

    When restricted by username, returns information about specific user.

    Views 3

    Returns list of types on the server. Restricted by:

    • Schema name
    • View name
    • Definer

    Samples

    The following code fragment is an elegant way to detect existence of a table.

    string tableName = "dept";
    if (myDbConnection.GetSchema("Tables", new string[] { "Test", tableName }).Rows.Count > 0)
    {
    Console.WriteLine("Table " + tableName + " exists in the database.");
    }
    
    
    Dim tableName As String = "dept"
    Dim restrictions() As String = {"Test", tableName}
    If (myDbConnection.GetSchema("Tables", restrictions).Rows.Count > 0) Then
      Console.WriteLine("Table " + tableName + " exists in the database.")
    End If
    
    

    The next sample shows how to retrieve columns information from a table and render it to console.

    static void GetTableInfo(PgSqlConnection myDbConnection, string tableName)
    {
      myDbConnection.Open();
      DataTable myDataTable = myDbConnection.GetSchema("Columns", new string[] { "Test", tableName });
      for (int i = 0; i < myDataTable.Columns.Count; i++)
      {
        Console.Write(myDataTable.Columns[i].Caption + "\t");
      }
      Console.WriteLine();
      foreach (DataRow myRow in myDataTable.Rows)
      {
        foreach (DataColumn myCol in myDataTable.Columns)
        {
          Console.Write(myRow[myCol] + "\t");
        }
        Console.WriteLine();
      }
      myDbConnection.Close();
    }
    
    
    Public Sub GetTableInfo(ByVal myDbConnection As PgSqlConnection, ByVal tableName As String)
      myDbConnection.Open()
      Dim restrictions() As String = {"Test", tableName}
      Dim myDataTable As DataTable = myDbConnection.GetSchema("Columns", restrictions)
      Dim i As Int32
      For i = 0 To myDataTable.Columns.Count - 1
        Console.Write(myDataTable.Columns(i).Caption & Chr(9))
      Next
      Console.WriteLine()
      Dim myRow As DataRow
      Dim myCol As DataColumn
      For Each myRow In myDataTable.Rows
        For Each myCol In myDataTable.Columns
          Console.Write(myRow(myCol) & Chr(9))
        Next
        Console.WriteLine()
      Next
      myDbConnection.Close()
    End Sub
    
    

    The following sample demonstrates how to generate SQL CREATE TABLE statement basing on metadata retrieved with GetSchema method. The generated script will work with all database management systems that support ANSI standard. Only column name and type are included in the script.

    static void GetCreateTable(PgSqlConnection myDbConnection, string tableName)
    {
      //Open the connection
      myDbConnection.Open();
      //Fill DataTable with columns information
      DataTable myDataTable = myDbConnection.GetSchema("Columns", new string[] { "Test", tableName });
      string queryText = "CREATE TABLE " + tableName + " (\n";
      string fieldLine;
      DataRow myRow;
      //For every row in the table
      for (int i = 0; i < myDataTable.Rows.Count; i++)
      {
        //Get column name and type
        myRow = myDataTable.Rows[i];
        fieldLine = myRow[0] + " " + myRow[1];
        //Add coma or closing bracket
        if (i < myDataTable.Rows.Count - 1)
        {
          fieldLine = fieldLine + ",\n";
        }
        else
        {
          fieldLine = fieldLine + ")";
        }
        //Add new column to script
        queryText = queryText + fieldLine;
      }
      Console.WriteLine(queryText);
      //Close the connection
      myDbConnection.Close();
    }
    
    
    Public Sub GetCreateTable(ByVal myDbConnection As PgSqlConnection, ByVal tableName As String)
      'Open the connection
      myDbConnection.Open()
      Dim restrictions() As String = {"Test", tableName}
      'Fill DataTable with columns information
      Dim myDataTable As DataTable = myDbConnection.GetSchema("Columns", restrictions)
      Dim queryText As String = "CREATE TABLE " + tableName + " (" + System.Environment.NewLine
      Dim fieldLine As String
      Dim myRow As DataRow
      Dim i As Int32
      'For every row in the table
      For i = 0 To myDataTable.Rows.Count - 1
        'Get column name and type
        myRow = myDataTable.Rows(i)
        fieldLine = myRow(0) & " " & myRow(1)
        'Add coma or closing bracket
        If (i < myDataTable.Rows.Count - 1) Then
          fieldLine = fieldLine + "," + System.Environment.NewLine
        Else
          fieldLine = fieldLine + ")"
        End If
        'Add new column to script
        queryText = queryText + fieldLine
      Next
      Console.WriteLine(queryText)
      'Close the connection
      myDbConnection.Close()
    End Sub
    
    

    Also you can get a metadata of query result set using the GetSchemaTable method of PgSqlDataReader and the ShemaTable property of PgSqlDataTable classes.

    See Also

    PgSqlConnection Class | DbConnectionBase.GetSchema Method