pg_database

Query structure information from a PostgreSQL database.

Extract information on these structures from a database:

• schemas (non-system only)
• tables
• columns
• indexes
• views

Extraction of these structures has not been implemented yet:

• table inheritance
• sequences
• triggers
• functions

Note

You have to call pg_query.db_init() with a PostgreSQL connection string in advance.

pg_jts.pg_database.get_columns(schema_name, table_name)

Return the column properties for given table_name and schema_name.

Return a list of dictionaries with these keys:

• column_name:
• datatype:
• ordinal_pos:
• null:
• column_default:
• column_comment:

pg_jts.pg_database.get_constraints(schema_name, table_name)

Return constraints for a table, one per constraint and per column.

Constraint types are:

• c: check constraint
• f: foreign key constraint
• p: primary key constraint
• u: unique constraint
• t: constraint trigger
• x: exclusion constraint

For each constraint the results are ordered by ordinal_position.

pg_jts.pg_database.get_database()

Return the name of the current database.

Returns a string.

pg_jts.pg_database.get_database_description()

Return the comment on the database.

Returns a string.

pg_jts.pg_database.get_functions(schema_name)

Return a list of triggers within a schema with given name.

NOT IMPLEMENTED; TODO:

pg_jts.pg_database.get_indexes(schema_name, table_name)

Return a list of indexes for a table within a schema.

Each index is described by a dictionary as described in pg_jts.pg_jts.

pg_jts.pg_database.get_now()

Return the current datetime from PostgreSQL.

Returns a string.

pg_jts.pg_database.get_schemas()

Return a list of all non-system schemas.

Each schema is described by a dictionary with following keys:

• schema_name: name of the schema
• schema_comment: the PostgreSQL comment characterizing the schema

pg_jts.pg_database.get_sequences(schema_name)

Return a list of sequences within a schema with given name.

NOT IMPLEMENTED; TODO:

SELECT * FROM information_schema.sequences;

pg_jts.pg_database.get_server_version()

Return the server version number.

Returns a string.

pg_jts.pg_database.get_tables(schema_name)

Return a list of all tables within a schema.

Each table is described by a dictionary with following keys:

• table_name: name of the table
• table_comment: the PostgreSQL comment describing the table

pg_jts.pg_database.get_triggers(schema_name)

Return a list of triggers within a schema with given name.

NOT IMPLEMENTED; TODO:

SELECT * FROM information_schema.triggers;

pg_jts.pg_database.get_views(schema_name)

Return a list of views within a schema of given name.

Each view is described by a dictionary having these keys:

• view_name: the name of the view (i.e. of the virtual table)
• view_definition: the SELECT statement defining the view

Developer hints

PostgreSQL documentation:

• http://www.postgresql.org/docs/current/static/catalogs.html
• http://www.postgresql.org/docs/current/static/functions-info.html

To see the queries executed when displaying schema information with psql, just call psql with option -E.