43.39. Exemplos

Exemplo 43-1. Colunas das chaves primárias

Este exemplo mostra uma consulta que lista os esquemas, nomes das tabelas e nomes das colunas das chaves primárias de um banco de dados. Exemplo tirado da lista de discussão pgsql-sql .

=> CREATE TABLE teste1 (id INT, texto TEXT, PRIMARY KEY (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "teste1_pkey" for table "teste1"
CREATE TABLE

=> CREATE TABLE teste2 (id1 INT, id2 INT, texto TEXT, PRIMARY KEY (id1,id2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "teste2_pkey" for table "teste2"
CREATE TABLE

=> \dt

         Lista de relações
 Esquema |  Nome  |  Tipo  | Dono
---------+--------+--------+-------
 public  | teste1 | tabela | teste
 public  | teste2 | tabela | teste
(2 linhas)

=> SELECT
->    pg_namespace.nspname AS esquema,
->    pg_class.relname AS tabela,
->    pg_attribute.attname  AS coluna
-> FROM pg_class
->    JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND
->         pg_namespace.nspname NOT LIKE 'pg_%'
->    JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND
->         pg_attribute.attisdropped='f'
->    JOIN pg_index ON pg_index.indrelid=pg_class.oid AND
->         pg_index.indisprimary='t' AND
->         (
(>         pg_index.indkey[0]=pg_attribute.attnum OR
(>         pg_index.indkey[1]=pg_attribute.attnum OR
(>         pg_index.indkey[2]=pg_attribute.attnum OR
(>         pg_index.indkey[3]=pg_attribute.attnum OR
(>         pg_index.indkey[4]=pg_attribute.attnum OR
(>         pg_index.indkey[5]=pg_attribute.attnum OR
(>         pg_index.indkey[6]=pg_attribute.attnum OR
(>         pg_index.indkey[7]=pg_attribute.attnum OR
(>         pg_index.indkey[8]=pg_attribute.attnum OR
(>         pg_index.indkey[9]=pg_attribute.attnum
(>         )
-> ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname;

 esquema | tabela | coluna
---------+--------+--------
 public  | teste1 | id
 public  | teste2 | id1
 public  | teste2 | id2
(3 linhas)

Neste exemplo há um limite para as 10 primeiras colunas da chave primária, porque o PostgreSQL 7.4.1 não permite a expressão attnum = ANY (indkey).

Exemplo 43-2. Chaves estrangeiras das tabelas

Este exemplo mostra uma consulta que lista os nomes dos esquemas, tabelas, colunas e chaves das chaves estrangeiras, e os nomes dos esquemas, tabelas e colunas referenciadas. Exemplo tirado da lista de discussão pgsql-sql .

=> CREATE TEMPORARY TABLE t1 (id SERIAL PRIMARY KEY, nome TEXT);
=> CREATE TEMPORARY TABLE t2 (id INT REFERENCES t1, nome TEXT);
=> SELECT
->    n.nspname AS esquema,
->    cl.relname AS tabela,
->    a.attname AS coluna,
->    ct.conname AS chave,
->    nf.nspname AS esquema_ref,
->    clf.relname AS tabela_ref,
->    af.attname AS coluna_ref,
->    pg_get_constraintdef(ct.oid) AS criar_sql
-> FROM pg_catalog.pg_attribute a
->    JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r')
->    JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace)
->    JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND
(>         ct.confrelid != 0 AND ct.conkey[1] = a.attnum)
->    JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r')
->    JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace)
->    JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND
(>         af.attnum = ct.confkey[1]);

  esquema  | tabela | coluna | chave | esquema_ref | tabela_ref | coluna_ref |           criar_sql
-----------+--------+--------+-------+-------------+------------+------------+------------------------------------
 pg_temp_1 | t2     | id     | $1    | pg_temp_1   | t1         | id         | FOREIGN KEY (id) REFERENCES t1(id)
(1 linha)