PostgreSQL Prático
151 pág.

PostgreSQL Prático


DisciplinaSistema Gerenciador de Banco de Dados840 materiais1.249 seguidores
Pré-visualização35 páginas
| tabela | postgres
public | opt_estado | tabela | postgres
Retornando o OID e o Esquema de uma Tabela
SELECT c.oid AS "OID",
 n.nspname AS "Esquema",
 c.relname AS "Tabela"
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
 AND c.relname ~ '^opt_cidades$'
ORDER BY 2, 3;
Retorno:
Metadados 97
 OID | Esquema | Tabela
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 TEMP TABLE teste1 (id INT, texto TEXT, PRIMARY KEY (id));
CREATE TEMP TABLE teste2 (id1 INT, id2 INT, texto TEXT, PRIMARY KEY (id1,id2));
\dt
SELECT
 pg_namespace.nspname AS esquema,
 pg_class.relname AS tabela,
 pg_attribute.attname AS coluna_pk
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;
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
Metadados 98
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]);
Retorno:
esquema | tabela | coluna | chave | esquema_ref | tabela_ref | coluna_ref | 
criar_sql
pg_temp_1 | t2 | id | t2_id_fkey | pg_temp_1 | t1 | id | FOREIGN KEY (id) 
REFERENCES t1(id)
SELECT a.attnum, a.attname AS field, t.typname as type, a.attlen AS length, a.atttypmod-4 as lengthvar, a.attnotnull
as notnull
 FROM pg_class c, pg_attribute a, pg_type t
 WHERE c.relname = 'apagar' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
 ORDER BY a.attnum;
Metadados 99
Saída:
ID do campo, nomecampo, tipo, tamanho, nulo/nãonulo
Outros
SELECT ic.relname AS index_name, bc.relname AS tab_name, ta.attname AS column_name, i.indisunique AS
unique_key, i.indisprimary AS primary_key
 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia
 WHERE (bc.oid = i.indrelid)
 AND (ic.oid = i.indexrelid)
 AND (ia.attrelid = i.indexrelid)
 AND (ta.attrelid = bc.oid)
 AND (bc.relname = 'apagar')
 AND (ta.attrelid = i.indrelid)
 AND (ta.attnum = i.indkey[ia.attnum-1])
 ORDER BY index_name, tab_name, column_name
Saída:
nomeindex/chave, nometabela, nomecampo, unique(t/f), nomepk (t/f)
SELECT rcname as index_name, rcsrc
 FROM pg_relcheck, pg_class bc
 WHERE rcrelid = bc.oid
 AND bc.relname = 'apagar'
 AND NOT EXISTS (
 SELECT *
 FROM pg_relcheck as c, pg_inherits as i
 WHERE i.inhrelid = pg_relcheck.rcrelid
 AND c.rcname = pg_relcheck.rcname
 AND c.rcsrc = pg_relcheck.rcsrc
 AND c.rcrelid = i.inhparent
 )
Saída: retorna as constraints check.
SELECT pg_class.relname, pg_attribute.attname, pg_type.typname, pg_attribute.atttypmod-4 
 FROM pg_class, pg_attribute, pg_type 
 WHERE pg_attribute.attrelid = pg_class.oid 
 AND pg_attribute.atttypid = pg_type.oid 
Metadados 100
 AND pg_class.relname = 'apagar' 
 AND pg_attribute.attname = 'descricao'
Saída: tabela, campo, tipo, tamanho (varchar)
Outros Exemplos
create table tabela_exemplo (
campo_1 integer default 5, campo_2 text default 'exemplo', campo_3 float(10),
campo_4 serial, campo_5 double precision, campo_6 int8, campo_7 Point,
campo_8 char(3), campo_9 varchar(17) );
Depois de criada a tabela vamos criar a consulta que nos retornará as informações da tabela:
SELECT
rel.nspname AS Esquema, rel.relname AS Tabela, attrs.attname AS Campo, "Type", "Default", attrs.attnotnull AS
"NOT NULL"
FROM (
SELECT c.oid, n.nspname, c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
JOIN (
SELECT a.attname, a.attrelid, pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as "Default", a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs ON (attrs.attrelid = rel.oid )
WHERE relname = 'tabela_exemplo' ORDER BY attrs.attnum;
Retorno: testes-# WHERE relname = 'tabela_exemplo' ORDER BY attrs.attnum;
esquema | tabela | campo | Type | Default | NOT NULL
Antes de tudo devemos criar um novo tipo de dado relacionado ao retorno que obteremos da função:
CREATE TYPE tabela_estrutura AS (Esquema text, Tabela text, Campo text, Tipo text, Valor text, AutoIncremento
bool);
A função abaixo é definida em PL/PgSQL, linguagem procedural muito semelhante ao PL/SQL do Oracle. A
função foi criada nesta linguagem devido a certas limitações que as funções em SQL possuem.
CREATE OR REPLACE FUNCTION Dados_Tabela(varchar(30))
 RETURNS SETOF tabela_estrutura AS '
DECLARE
 r tabela_estrutura%ROWTYPE;
 rec RECORD;
 vTabela alias for $1;
 eSql TEXT;
BEGIN
 eSql := SELECT
 CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) , CAST(attrs.attname AS TEXT), 
 CAST("Type" AS TEXT), CAST("Default" AS TEXT), attrs.attnotnull
Metadados 101
 FROM
 (SELECT c.oid, n.nspname, c.relname
 FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
 JOIN 
 (SELECT a.attname, a.attrelid,
 pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
 (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
 WHERE d.adrelid