PostgreSQL Prático/Metadados

Origem: Wikilivros, livros abertos por um mundo aberto.

12 - Metadados (Catálogo)[editar | editar código-fonte]

Metadados 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';