PostgreSQL Metadata

 

http://www.devart.com/dotconnect/postgresql/docs/MetaData.html

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("AggregateFunctions") returns the list of the function arguments. The following restrictions may be specified: Schema name Function name User name Type Function OID (for overload functions)
Casts	3	GetSchema("Casts") returns list of casts available on the server. The first restriction for this collection is namespace of a cast. The second restriction is source type for the cast. The third restriction is target type for the cast.
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 a name of a database. The second restriction is definer's name. The last restriction is encoding of the database.
DatasourceInformation	1	Returns information about data sources. The only restriction is schema name.
Domains	9	Returns list of domains on the server. The restrictions for this collection are the following: Domain name Definer OID type Base type Type length Typmod Number of dimensions Schema name OID
ForeignKeys	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: Function name Schema 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: Index name Schema name Table name OID
Languages	2	Lists all languages on the server. The first restriction is a language name, the second is OID.
NestedTables	5	Lists nested tables on the server. Restricted by: Table name Schema name Parent table name Parent schema name OID
Operators	2	Returns list of operators allowed on the server. Restrict it by OID and name.
PrimaryKeys	3	Returns list of primary keys on the server. First restriction is key name. Second is schema name Third is table name
Schemas	2	Returns list of schemas on the server. The first restriction is OID The second restriction is schema name
Tables	4	Returns list of tables on the server. Restrict the collection with: OID Table name Schema name Definer
Triggers	6	Returns list of triggers on the server. Restricted by: OID Trigger name Schema name Definer Language Arguments count
Types	5	Returns list of types on the server. Restricted by: OID Type name Schema name Definer Type Length
Users	1	Lists all users on the server. When restricted by username, returns information about specific user.
Views	4	Returns list of types on the server. Restricted by: OID View name Schema name Definer

Npgsql 集合：

MetaDataCollections
Restrictions
Databases
Tables
Columns
Views
Users

https://github.com/npgsql/Npgsql/releases/tag/v2.2.4.3

http://npgsql.projects.pgfoundry.org/docs/api/Npgsql.NpgsqlConnection.GetSchema_overloads.html

http://www.codeproject.com/Articles/37154/PostgreSQL-PostGis-Operations

数据类型：

bit	System.Int64	1560
varbit	System.Int64	1560
bit varying	System.Int64	1560
bool	System.Boolean	16
boolean	System.Boolean	16
smallint	System.Int16	21
int	System.Int32	23
integer	System.Int32	23
int4	System.Int32	23
serial	System.Int32	23
serial4	System.Int32	23
bigint	System.Int64	20
int8	System.Int64	20
bigserial	System.Int64	20
serial8	System.Int64	20
float	System.Single	700
real	System.Single	700
float4	System.Single	700
double	System.Double	701
double precision	System.Double	701
decimal	System.Decimal	1700
numeric	System.Decimal	1700
dec	System.Decimal	1700
date	System.DateTime	1082
timestamp	System.DateTime	1114
timestamptz	System.DateTime	1184
time	System.TimeSpan	1083
timetz	System.TimeSpan	1266
interval	System.TimeSpan	1186
char	System.String	1042
character	System.String	1042
uuid	System.Guid	2950
varchar	System.String	1043
character varying	System.String	1043
text	System.String	25
binary	System.Byte[]	17
bytea	System.Byte[]	17
cidr	System.String	29
circle	System.String	718
inet	System.String	869
line	System.String	628
lseg	System.String	601
macaddr	System.String	829
box	System.String	603
path	System.String	602
point	System.String	600
polygon	System.String	604
money	System.Double	790

 集合：

MetaDataCollections

ReservedWords

AggregateFunctions

Arguments

Casts

CompositeTypeAttributes

Columns

DataSourceInformation

DataTypes

Databases

Domains

ForeignKeys

ForeignKeyColumns

Functions

Groups

Indexes

Indexcolumns

Languages

NestedTables

Operators

OperatorClasses

PrimaryKeys

Rules

Schemas

Sequences

Tables

Triggers

Types

Users

UserPrivileges

UniqueKeys

Views

Restrictions

Postgresql  NpgsqlDbType System.DbType Enum .Net System Type
----------  ------------ ------------------ ----------------
int8        Bigint       Int64              Int64
bool        Boolean      Boolean            Boolean
bytea       Bytea        Binary             Byte[]
date        Date         Date               DateTime
float8      Double       Double             Double
int4        Integer      Int32              Int32
money       Money        Decimal            Decimal
numeric     Numeric      Decimal            Decimal
float4      Real         Single             Single
int2        Smallint     Int16              Int16
text        Text         String             String
time        Time         Time               DateTime
timetz      Time         Time               DateTime
timestamp   Timestamp    DateTime           DateTime
timestamptz TimestampTZ  DateTime           DateTime
interval    Interval     Object             TimeSpan
varchar     Varchar      String             String
inet        Inet         Object             IPAddress
bit         Bit          Boolean            Boolean
uuid        Uuid         Guid               Guid
array       Array        Object             Array