Sat 18 February 2017

Postgres Information Functions

Postgres contains a wealth of functions that provide information about a database and the objects within. The System Information Functions of the official documention provides a full list. There are a huge number of functions covering a whole host of info from the current database session, privileges, function properties.


Find an objects oid

A lot of the info functions accept the Object Identifier Type for objects in the database. This can be obtained by casting to regclass (also described in the oid docs) then to oid:

select 'schema_name.relation_name'::regclass::oid;

Where relation_name is a table, view, index etc.

View definition

select pg_get_viewdef('schema_name.view_name'::regclass::oid);

Or in psql you can use one of the built in commands:

\d+ schema_name.view_name

Function definition

Returns the function definition for a given function. Many built-in functions don't reveal much due to them not being written in SQL but for those that are you'll get the complete create function statement. For example to view the definition of the PostGIS st_colormap function:

select pg_get_functiondef('st_colormap(raster, integer, text, text)'::regprocedure);


A whole host of functions exist to determine privileges for schemas, tables, functions etc. Some examples:

Determine if the current users can select from a table:

select has_table_privilege('schema_name.relation_name', 'select');

Note: The docs state that "multiple privilege types can be listed separated by commas, in which case the result will be true if any of the listed privileges is held". This means that in order to test a number of privileges it is normally better to test each privilege individually as select has_table_privilege('schema_name.relation_name', 'select,update'); would return t even if only select is supported.

Determine if a user can use a schema:

select has_schema_privilege('schema_name', 'usage');