PostgreSQL Prático/Metadados
12 - Metadados (Catálogo)
editarMetadados são dados sobre dados.
Uma consulta normal retorna informações existentes em tabelas, já uma consulta sobre os metadados retorna informações sobre os bancos, os objetos dos bancos, os campos de tabelas, seus tipos de dados, seus atributos, suas constraints, etc.
Retornar Todas as Tabelas do banco e esquema atual
SELECT schemaname AS esquema, tablename AS tabela, tableowner AS dono
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY schemaname, tablename
Informações de Todos os Tablespaces
SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation FROM pg_catalog.pg_tablespace
Retornar banco, dono, codificação, comentários e tablespace
SELECT pdb.datname AS banco, pu.usename AS dono, pg_encoding_to_char(encoding) AS codificacao, (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid) AS comentario, (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=pdb.dattablespace) AS tablespace FROM pg_database pdb, pg_user pu WHERE pdb.datdba = pu.usesysid ORDER BY pdb.datname
Tabelas, donos, comentários, registros e tablespaces de um schema
SELECT c.relname as tabela, pg_catalog.pg_get_userbyid(c.relowner) AS dono, pg_catalog.obj_description(c.oid, 'pg_class') AS comentario, reltuples::integer as registros, (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND nspname='public' ORDER BY c.relname
Mostrar Sequences de um Esquema
SELECT c.relname AS seqname, u.usename AS seqowner, pg_catalog.obj_description(c.oid, 'pg_class') AS seqcomment,
(SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_user u, pg_catalog.pg_namespace n
WHERE c.relowner=u.usesysid AND c.relnamespace=n.oid
AND c.relkind = 'S' AND n.nspname='public' ORDER BY seqname
Mostrar Tablespaces
SELECT spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocation FROM pg_catalog.pg_tablespace
Mostrar detalhes de uma function
SELECT pc.oid AS prooid, proname, lanname as prolanguage, pg_catalog.format_type(prorettype, NULL) as proresult, prosrc, probin, proretset, proisstrict, provolatile, prosecdef, pg_catalog.oidvectortypes(pc.proargtypes) AS proarguments, proargnames AS proargnames, pg_catalog.obj_description(pc.oid, 'pg_proc') AS procomment FROM pg_catalog.pg_proc pc, pg_catalog.pg_language pl WHERE pc.oid = 'oid_da_function'::oid AND pc.prolang = pl.oid
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]);
Mostrar Esquemas e Tabelas
SELECT n.nspname as esquema, c.relname as tabela, a.attname as campo, format_type(t.oid, null) as tipo_de_dado
FROM pg_namespace n, pg_class c,
pg_attribute a, pg_type t
WHERE n.oid = c.relnamespace
and c.relkind = 'r' -- no indices
and n.nspname not like 'pg\\_%' -- no catalogs
and n.nspname != 'information_schema' -- no information_schema
and a.attnum > 0 -- no system att's
and not a.attisdropped -- no dropped columns
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY nspname, relname, attname;
Mostrar Esquemas e respectivas tabelas do Banco atual:
SELECT n.nspname as esquema, c.relname as tabela FROM pg_namespace n, pg_class c WHERE n.oid = c.relnamespace and c.relkind = 'r' -- no indices and n.nspname not like 'pg\\_%' -- no catalogs and n.nspname != 'information_schema' -- no information_schema ORDER BY nspname, relname
Contar Todos os Registros de todas as tabelas de todos os bancos:
<?php
$conexao=pg_connect("host=127.0.0.1 user=postgres password=postabir");
$sql="SELECT datname AS banco FROM pg_database ORDER BY datname";
$consulta=pg_query($conexao,$sql);
$banco = array();
$c=0;
while ($data = @pg_fetch_object($consulta,$c)) {
$cons=$data->banco;
$banco[] .= $cons;
$c++;
}
$sql2="SELECT n.nspname as esquema,c.relname as tabela FROM pg_namespace n, pg_class c
WHERE n.oid = c.relnamespace
and c.relkind = 'r' -- no indices
and n.nspname not like 'pg\\_%' -- no catalogs
and n.nspname != 'information_schema' -- no information_schema
ORDER BY nspname, relname";
for ($x=0; $x < count($banco);$x++){
if ($banco[$x] !="template0" && $banco[$x] != "template1" && $banco[$x] !="postgres"){
$conexao2=pg_connect("host=127.0.0.1 dbname=$banco[$x] user=postgres password=postabir");
$consulta2=pg_query( $conexao2, $sql2 );
while ($data = pg_fetch_object($consulta2)) {
$esquematab=$data->esquema.'.'.$data->tabela;
$sql3="SELECT count(*) FROM $esquematab";
$consulta3=pg_query($conexao2,$sql3);
$res=@pg_fetch_array($consulta3);
print 'Banco.Esquema.Tabela ->
'.$banco[$x].'.'.$data->esquema.'.'.$data->tabela.' - Registro(s) - '.$res[0].'
';
$total += $res[0];
}
}
}
print "Total de Registro de todas as tabelas de todos os bancos ". $total;
?>
Dado o banco de dados, qual o seu diretório:
select datname, oid from pg_database;
Dado a tabela, qual o seu arquivo:
select relname, relfilenode from pg_class;
Mostrar chaves primárias das tabelas do esquema public
select indexrelname as indice, relname as tabela from pg_catalog.pg_statio_user_indexes as A INNER JOIN pg_catalog.pg_index as B ON A.indexrelid=B.indexrelid WHERE A.schemaname='public' AND B.indisprimary = true;
Para visualizar como as consultas são feitas internamente via psql usamos o comando assim:
psql -U user banco -E
Vamos usar o banco municipios, criado com os municípios do Brasil. A tabela opt_cidades.
Veja Um Exemplo Que Retorna a Chave Primária da Tabela opt_cidades
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 = 'opt_cidades' AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] ORDER BY index_name, tab_name, column_name;
Retornará:
index_name | tab_name | column_name | unique_key | primary_key
opt_cidades_pkey | opt_cidades | id | t | t
Retornando o Nome do Esquema
SELECT n.nspname AS "Esquema"
FROM pg_catalog.pg_namespace AS n,
pg_catalog.pg_class AS c
WHERE c.relnamespace = n.oid AND c.relname='opt_cidades';
Retorno: Esquema
Retornar nomes de bancos:
SELECT datname AS banco FROM pg_database
WHERE datname != 'template0' and datname != 'template1' and datname != 'postgres' ORDER BY datname
Retornar nomes e OIDs dos bancos:
SELECT oid, datname FROM pg_database;
Dado a tabela, qual o seu arquivo:
select relname, relfilenode from pg_class;
No Windows
Podemos passar parâmetros para as macros, por exemplo:
doskey /exename=psql.exe dbinfo=SELECT datname,pg_encoding_to_char(encoding) FROM pg_database WHERE datname='$1';
E então apenas passar o parâmetro na linha de comando:
postgres=# dbinfo postgres
Listar tabelas, e dono do esquema atual:
SELECT n.nspname as "Schema",
c.relname as "Tabela",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Tipo",
u.usename as "Dono"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r',)
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Listar Tabelas
select c.relname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r',) AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql\_%'
Listar todas as tabelas, índices, tamanho em KB e OIDs:
VACUUM; --Executar antes este comando
SELECT c1.relname AS tabela, c2.relname AS indice,
c2.relpages * 8 AS tamanho_kb, c2.relfilenode AS arquivo
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid
UNION
SELECT relname, NULL, relpages * 8, relfilenode
FROM pg_class
WHERE relkind = 'r'
ORDER BY tabela, indice DESC, tamanho_kb;
Tabelas e Soma
SELECT tablename, SUM( size_kb )
FROM
( SELECT c1.relname AS "tablename",
c2.relpages * 8 AS "size_kb"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
AND i.indexrelid = c2.oid
UNION
SELECT relname, relpages * 8
FROM pg_class
WHERE relkind = 'r' ) AS relations
GROUP BY tablename;
-- r = ordinary table, i = index, S = sequence, v = view, c = composite type, -- s = special, t = TOAST table
Tamanho em bytes de um banco:
select pg_database_size('banco');
Tamanho em bytes de uma tabela:
pg_total_relation_size('tabela')
Tamanho em bytes de tabela ou índice:
pg_relation_size('tabelaouindice')
Lista donos e bancos:
SELECT rolname as dono, datname as banco
FROM pg_roles, pg_database
WHERE pg_roles.oid = datdba
ORDER BY rolname, datname;
Nomes de bancos:
select datname from pg_database where datname not in ('template0','template1') order by 1
Nomes e colunas:
select tablename,'T' from pg_tables where tablename not like 'pg\_%' and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages', 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
union
select viewname,'V' from pg_views where viewname not like 'pg\_%'
Tamanho de esquema e índice:
SELECT nspname,
sum(relpages * cast( 8192 AS bigint )) as "table size",
sum( ( select sum(relpages)
from pg_class i, pg_index idx
where i.oid = idx.indexrelid
and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size",
sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages)
from pg_class i, pg_index idx
where i.oid = idx.indexrelid
and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size"
FROM pg_class t, pg_namespace
WHERE relnamespace = pg_namespace.oid
and pg_namespace.nspname not like 'pg_%'
and pg_namespace.nspname != 'information_schema'
and relkind = 'r' group by nspname;
Retornando Tabelas e Seus Donos de um Esquema
SELECT n.nspname as "public",
c.relname as "opt_cidades",
CASE c.relkind WHEN 'r' THEN 'tabela' WHEN 'v' THEN 'view' WHEN 'i' THEN 'índice' WHEN 'S' THEN
'sequencia' WHEN 's' THEN 'especial' END as "Tipo", u.usename as "Dono"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r',)
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Retorno:
public | opt_cidades | Tipo | Dono
+------------------+--------+----------
public | opt_cidades | 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:
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 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;
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 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
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 LIKE '% || vTabela || %'
ORDER BY attrs.attnum;
FOR r IN EXECUTE eSql
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
RAISE EXCEPTION Tabela % não encontrada, vTabela;
END IF;
RETURN;
END
'
LANGUAGE 'plpgsql';
Para utilizar esta função, utilize o seguinte comando:
SELECT * FROM Dados_Tabela('tabela');
Retorno:
esquema | tabela | campo | tipo | valor | autoincremento
Exemplos contidos no arquivo:
/usr/local/src/postgresql-8.1.3/src/tutorial/syscat.sql
SELECT rolname as "Donos", datname as Bancos FROM pg_roles, pg_database WHERE pg_roles.oid = datdba ORDER BY rolname, datname;
Retorno: Donos e Bancos
SELECT n.nspname as esquema, c.relname as tabela FROM pg_class c, pg_namespace n WHERE c.relnamespace=n.oid and c.relkind = 'r' -- not indices, views, etc and n.nspname not like 'pg\\_%' -- not catalogs and n.nspname != 'information_schema' -- not information_schema ORDER BY nspname, relname;
Retorno: Esquemas e Tabelas
SELECT n.nspname as esquema, c.relname as tabela, a.attname as campo, format_type(t.oid, null) as tipo_de_dado
FROM pg_namespace n, pg_class c,
pg_attribute a, pg_type t
WHERE n.oid = c.relnamespace
and c.relkind = 'r' -- no indices
and n.nspname not like 'pg\\_%' -- no catalogs
and n.nspname != 'information_schema' -- no information_schema
and a.attnum > 0 -- no system att's
and not a.attisdropped -- no dropped columns
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY nspname, relname, attname;
Retorno: esquemas, tabelas, campos, tipos de dados
SELECT n.nspname, o.oprname AS binary_op,
format_type(left_type.oid, null) AS left_opr,
format_type(right_type.oid, null) AS right_opr,
format_type(result.oid, null) AS return_type
FROM pg_namespace n, pg_operator o, pg_type left_type,
pg_type right_type, pg_type result
WHERE o.oprnamespace = n.oid
and o.oprkind = 'b' -- binary
and o.oprleft = left_type.oid
and o.oprright = right_type.oid
and o.oprresult = result.oid
ORDER BY nspname, left_opr, right_opr;
Retorno: operadores binários
Baypassar os de sistema:
and n.nspname not like 'pg\\_%' -- no catalogs
SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
FROM pg_namespace n, pg_aggregate a,
pg_proc p, pg_type t
WHERE p.pronamespace = n.oid
and a.aggfnoid = p.oid
and p.proargtypes[0] = t.oid
ORDER BY nspname, proname, typname;
Retorno: lista todas as funções agregadas e os tipos que podem ser aplicados
Dado o banco de dados, qual o seu diretório:
select datname, oid from pg_database;
Dado a tabela, qual o seu arquivo:
select relname, relfilenode from pg_class;
Exemplo que retorna índice, campo, tipo, comprimento, null, default:
SELECT pg_attribute.attnum AS index,
attname AS field,
typname AS type,
atttypmod-4 as length,
NOT attnotnull AS "null",
adsrc AS default
FROM pg_attribute,
pg_class,
pg_type,
pg_attrdef
WHERE pg_class.oid=attrelid
AND pg_type.oid=atttypid
AND attnum >0
AND pg_class.oid=adrelid
AND adnum=attnum
AND atthasdef='t'
AND lower(relname)='datas'
UNION
SELECT pg_attribute.attnum AS index,
attname AS field,
typname AS type,
atttypmod-4 as length,
NOT attnotnull AS "null",
AS default
FROM pg_attribute,
pg_class,
pg_type
WHERE pg_class.oid=attrelid
AND pg_type.oid=atttypid
AND attnum>0
AND atthasdef='f'
AND lower(relname)='datas';