In this issue we will take a look at some utility functions and views that access the system catalogs. These functions and views can help create dynamic queries or just enable you to see your schema more clearly. The information_schema can also help you see your schema more clearly as do the slash commands in psql. The source code for Primary Keys and Table Info Functions and Views are also be available via Tidbits. Contributors: elein at varlena.com

Primary Key Functions and Views System Catalog Access Functions and Views 26-Mar-2005

Finding the PRIMARY KEYS of any given table seems to be generating a lot of interest lately. A few functions and views for Primary Keys are shown here.

The following function from AndrewSN on IRC is interesting because it uses generate_series(). The field indkey is an array and generate_series is used to index this array and flatten out the array. We are using the maximum number of index keys to ensure we get them all. If you want to use this view in versions prior to 8.0 you will want to change current_setting('max_index_keys')::integer to 31 since current_settings() is not available.

   DROP VIEW pkview;
   CREATE VIEW pkview as
   SELECT n.nspname AS schema_name,
           c.relname AS table_name,
           c.oid AS table_oid,
           a.attname AS column_name,
           idx.n + 1 AS ordinal_position
      FROM pg_class c, pg_attribute a, pg_index i, pg_namespace n,
           generate_series(0, current_setting('max_index_keys')::integer ) idx(n)
     WHERE c.oid = a.attrelid
       AND c.oid = i.indrelid
       AND i.indisprimary
       AND a.attnum = i.indkey[idx.n]
       AND NOT a.attisdropped
       AND has_schema_privilege(n.oid, 'USAGE'::text)
       AND n.nspname NOT LIKE 'pg!_%' ESCAPE '!'
       AND has_table_privilege(c.oid, 'SELECT'::text)
       AND c.relnamespace = n.oid;

   gb=# select * from pkview;
    schema_name |  table_name  | table_oid | column_name | ordinal_position
   -------------+--------------+-----------+-------------+------------------
    public      | rr_types     |    151007 | rt_type     |                1
    public      | rr_reports   |    150996 | r_name      |                1
    public      | rr_appgroups |    150985 | ag_name     |                1
    public      | rr_rprompts  |    151001 | r_name      |                1
    public      | rr_rprompts  |    151001 | pr_name     |                2
    public      | rr_prompts   |    150990 | pr_name     |                1
    public      | keywords     |    150944 | issue       |                1
    public      | keywords     |    150944 | article     |                2
    public      | keywords     |    150944 | kw          |                3

   gb=# select column_name from pkview where table_name = 'keywords';
    column_name
   -------------
    issue
    article
    kw
   (3 rows)

Given this handy view we can now write a function ispk() which will tell us if any named column in a table is a primary key. This function depends on the pkview and there are two versions, one with an explicit schema name and one without.

   CREATE OR REPLACE FUNCTION ispk(text, text, text)
   RETURNS boolean AS
   $$
   BEGIN
      PERFORM column_name FROM pkview
      WHERE $3 = column_name AND $2 = table_name 
         AND schema_name = coalesce($1,'public'::text);
      IF FOUND THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
   END;
   $$ LANGUAGE 'plpgsql' HANDLES NULLS;
   
   CREATE OR REPLACE FUNCTION ispk(text, text)
   RETURNS boolean AS
   $$
      SELECT ispk( NULL, $1, $2 );
   $$ LANGUAGE 'sql';

   gb=# select ispk('keywords','article');
    ispk
   ------
    t
   (1 row)
   
   gb=# select ispk('xxx','keywords','article');
    ispk
   ------
    f
   (1 row)
   
   gb=# select ispk('public','keywords','article');
    ispk
   ------
    t
   (1 row)

The pkview is based on the system catalog table pg_index. An alternative to the pkview is a set returning function written by applejack and David Fetter. It relies on the pg_constraint table. The small problem with this function is that it does not guarantee that the keys will be returned in order. That is, a key made up of several different columns may return in any order. A primary key of (one, two) may return as (two, one). This is due to using ANY rather than using the generate_series() technique of expanding the column o.conkey. Changing the function to use generate_series() is an exercise for the reader.

   CREATE OR REPLACE FUNCTION get_pk_cols(text, text )
   RETURNS SETOF text
   LANGUAGE 'sql'
   AS $$
   SELECT a.attname::text AS "pk_column"
   FROM pg_class c JOIN pg_constraint o
      ON ( c.relname = $2 AND c.oid = o.conrelid AND o.contype='p')
      JOIN pg_attribute a ON (a.attrelid = c.oid AND a.attnum = ANY (o.conkey))
   JOIN pg_namespace n ON ( n.nspname = coalesce($1,'public'::text) AND c.relnamespace = n.oid)
   WHERE NOT a.attisdropped
       AND has_schema_privilege(n.oid, 'USAGE'::text)
       AND n.nspname NOT LIKE 'pg!_%' ESCAPE '!'
       AND has_table_privilege(c.oid, 'SELECT'::text)
   $$ ;
   
   CREATE OR REPLACE FUNCTION get_pk_cols(TEXT)
   RETURNS SETOF TEXT
   LANGUAGE 'sql'
   AS $$
      SELECT get_pk_cols(NULL, $1 );
   $$;

   gb=# select get_pk_cols('articles');
    get_pk_cols
   -------------
    issue
    article
   (2 rows)

It is interesting to see that the primary key can be viewed as either an index or a constraint. Technically, the primary key is a constraint, but in PostgreSQL it is implemented as a UNIQUE B-TREE index so either way works. The trick in both cases is to expand the array column in the system catalog which contains the column numbers of the primary keys in order to join the elements to the pg_attribute table.

The information_schema also displays primary keys as constraints. The names of the constraints are qualified by '%pkey' with the knowledge that primary key indexes are created that way.

   gb=# select constraint_name, table_schema, table_name, column_name, ordinal_position
   gb-# from key_column_usage
   gb-# where constraint_name like '%pkey';
            int_name  | table_schema |  table_name  | column_name | ordinal_position
   -------------------+--------------+--------------+-------------+------------------
    articles_pkey     | public       | articles     | issue       |                1
    articles_pkey     | public       | articles     | article     |                2
   ...

Contributors: elein at varlena.com, david at fetter.org, andrewsn at freenode.net, applejack at freenode.net,

View for Column Information Seeing Column Definitions 27-Mar-2005

Here is Table Info view that tells you all you want to know about your column data. The schema name, table name, table type and table OID are all called out for querying. The primary information for each column is its name, type, whether it is nullable, it's default if it has one and any CHECK constraint associated with the column.

   --
   -- Default Values of Columns in tables (added types and nullability)
   -- andrewsn/elein
   CREATE OR REPLACE VIEW column_info as
   SELECT n.nspname AS schema_name,
          r.relname AS table_name,
          (SELECT CASE WHEN r.relkind = 'r' THEN 'Table' ELSE 'View' END) AS kind,
          r.oid AS table_oid,
          a.attname AS column_name,
          pg_catalog.format_type(a.atttypid, a.atttypmod) AS column_type,
          (SELECT case WHEN a.attnotnull THEN 'NOT NULL' ELSE '' END) AS nullable,
          d.adsrc AS default_value,
          c.consrc AS check_constraint
     FROM pg_namespace n JOIN pg_class r      ON (r.relnamespace = n.oid)
                         JOIN pg_attribute a  ON (r.oid = a.attrelid)
              LEFT OUTER JOIN pg_attrdef d    ON (a.attrelid=d.adrelid AND a.attnum = d.adnum)
              LEFT OUTER JOIN pg_constraint c ON (c.conrelid=r.oid AND a.attnum = ANY (c.conkey) )
    WHERE
      NOT a.attisdropped
      AND a.attnum > 0
      AND has_schema_privilege(n.oid, 'USAGE'::text)
      AND n.nspname NOT LIKE 'pg!_%' ESCAPE '!'
      AND has_table_privilege(r.oid, 'SELECT'::text)
      AND pg_catalog.pg_table_is_visible(r.oid)
      AND (r.relkind = 'r' OR r.relkind = 'v')
   ORDER BY n.nspname, r.relname, a.attnum;

   Schema  | Table/View  | Kind  | Tbl OID | Column Name      | col type| Nullable | Default    |
      Constraint
   ---------------------------------------------------------------------------------------------
    public | arttext     | Table |  150923 | language         | text    | NOT NULL | 'en'::text |
    public | arttext     | Table |  150923 | mytitle          | text    |          |            |
    public | arttext     | Table |  150923 | ititle           | text    |          |            |
    public | arttext     | Table |  150923 | arttext          | text    |          |            |
    public | contrib     | Table |  150939 | issue            | integer | NOT NULL |            |
    public | contrib     | Table |  150939 | issue            | integer | NOT NULL |            |
    public | contrib     | Table |  150939 | issue            | integer | NOT NULL |            |
    public | contrib     | Table |  150939 | article          | integer | NOT NULL |            |
    public | contrib     | Table |  150939 | article          | integer | NOT NULL |            |
    public | contrib     | Table |  150939 | email            | text    | NOT NULL |            |
    public | contrib     | Table |  150939 | email            | text    | NOT NULL |            |
    public | iakeys      | View  |  150954 | issue            | integer |          |            |
    public | iakeys      | View  |  150954 | article          | integer |          |            |
    public | iakeys      | View  |  150954 | kw               | text    |          |            |
    public | issues      | Table |  150929 | issue            | integer | NOT NULL |            |
      ((issue > 0) AND (issue_date > '1959-12-31'::date))
    public | issues      | Table |  150929 | issue            | integer | NOT NULL |            |
    public | issues      | Table |  150929 | issue_date       | date    |          |            |
      ((issue > 0) AND (issue_date > '1959-12-31'::date))

A shorter more concise query can be used for viewing. This output has been changed to fold the check constraint column to the next line.

   gb=# SELECT schema_name || '.' || table_name || '.' || column_name AS column_name,
   column_type, nullable, default_value, check_constraint
   FROM column_info;
   gb=#  SELECT schema_name || '.' || table_name || '.' || column_name AS column_name,
   gb-# column_type, nullable, default_value, check_constraint
   gb-# FROM column_info where table_name = 'issues' or table_name='arttext';
          column_name        | column_type | nullable | default_value |
                     check_constraint
   --------------------------+-------------+----------+---------------+
    public.arttext.issue     | integer     | NOT NULL |               |
    public.arttext.issue     | integer     | NOT NULL |               |
    public.arttext.issue     | integer     | NOT NULL |               |
    public.arttext.article   | integer     | NOT NULL |               |
    public.arttext.article   | integer     | NOT NULL |               |
    public.arttext.language  | text        | NOT NULL | 'en'::text    |
    public.arttext.mytitle   | text        |          |               |
    public.arttext.ititle    | text        |          |               |
    public.arttext.arttext   | text        |          |               |
    public.issues.issue      | integer     | NOT NULL |               |
    public.issues.issue      | integer     | NOT NULL |               |
                    ((issue > 0) AND (issue_date > '1959-12-31'::date))
    public.issues.issue_date | date        |          |               |
                    ((issue > 0) AND (issue_date > '1959-12-31'::date))

Contributors: elein at varlena.com andrewsn at freenode.net