quarta-feira, 8 de setembro de 2010

Views com user functions para administração do PostgreSQL

Views com user functions para administração do PostgreSQL

--
-- Nome Artefato/Programa..: monitor_ddl.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 01/03/2010
-- Data Atual..............: 08/09/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: views para monitoramento das atividades do postgres 8.3 /8.4
-- OBS.....................:

-- Script de monitoramento de banco de dados Postgres 8.x DLL
-- Autor: O Peregrino

-- functions
--
-- Nome Artefato/Programa..: sp_tradutor.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 30/08/2010
-- Data Atual..............: 30/08/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para fazer tradução de english para portugues de algumas palavras usadas no postgres
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS sp_tradutor (TEXT) CASCADE;
CREATE OR REPLACE FUNCTION sp_tradutor (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo TEXT;
BEGIN
  stextonovo := REPLACE(stexto,'OR','OU');     
  stextonovo := REPLACE(stextonovo, 'AND','E');  
  stextonovo := REPLACE(stextonovo, 'NOT','NÃO');
  stextonovo := REPLACE(stextonovo, 'IS',''); 
  stextonovo := REPLACE(stextonovo, 'NULL','NULO');
  stextonovo := REPLACE(stextonovo, 'integer','inteiro');
  stextonovo := REPLACE(stextonovo, 'smallint','inteiro_pequeno');
  stextonovo := REPLACE(stextonovo, 'bigint','inteiro_grande');
  stextonovo := REPLACE(stextonovo, 'numeric','numerico');
  stextonovo := REPLACE(stextonovo, 'double precision','dupla_precisao');
  stextonovo := REPLACE(stextonovo, 'date','data');
  stextonovo := REPLACE(stextonovo, 'timestamp without time zone','data_hora');
  stextonovo := REPLACE(stextonovo, 'timestamp with time zone','data_hora');
  stextonovo := REPLACE(stextonovo, 'timestamp(0) without time zone','data_hora');
  stextonovo := REPLACE(stextonovo, 'time without time zone','hora');
  stextonovo := REPLACE(stextonovo, 'text','texto');
  stextonovo := REPLACE(stextonovo, 'character varying','v_caracter');
  stextonovo := REPLACE(stextonovo, 'character','caracter');
  stextonovo := REPLACE(stextonovo, '"char"','caracter');
  stextonovo := REPLACE(stextonovo, 'bpchar','caracter');
  stextonovo := REPLACE(stextonovo, 'boolean','logico');
  stextonovo := REPLACE(stextonovo, 'bytea','objeto_binario_grande');
  stextonovo := REPLACE(stextonovo, 'year','ano');  
  stextonovo := REPLACE(stextonovo, 'month','mes');  
  stextonovo := REPLACE(stextonovo, 'day','dia');  
  stextonovo := REPLACE(stextonovo, 'now()','agora()');  

  RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER;

--SELECT sp_tradutor('(year((data_cadastro)::timestamp with time zone) >= year(now()))') AS test1;

--
-- Nome Artefato/Programa..: sp_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 09/10/2008
-- Data Atual..............: 17/08/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS sp_rm_special_chars (TEXT);
CREATE OR REPLACE FUNCTION sp_rm_special_chars (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo TEXT;
BEGIN
  -- Tabela ASCII
  -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32
                                                  --  hex d abr ctl descricao
                                                  --  00  0 NUL ^@    Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata
  stextonovo := TRANSLATE(stexto, ' ','');       --  01  1 SOH ^A    Start of Header - Início do cabeçalho
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  02  2 STX ^B    Start of Text - Início do texto
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  03  3 ETX ^C    End of Text - Fim do texto
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  04  4 EOT ^D    End of Tape - Fim de fita
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  05  5 ENQ ^E    Enquire - Interroga identidade do terminal
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  06  6 ACK ^F    Acknowledge - Reconhecimento
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  07  7 BEL ^G    Bell - Campainha
  stextonovo := TRANSLATE(stextonovo, '\8','');   --  08  8 BS     ^H  Back-space - Espaço atrás
  stextonovo := TRANSLATE(stextonovo, '\9','');   --  09  9 HT     ^I  Horizontal Tabulation - Tabulação horizontal
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  0A 10 LF  ^J    Line-Feed - Alimenta linha
  stextonovo := TRANSLATE(stextonovo, '\11','');  --  0B 11 VT  ^K     Vertical Tabulation - Tabulação vertical
  stextonovo := TRANSLATE(stextonovo, '\12','');  --  0C 12 FF  ^L    Form-Feed - Alimenta formulário
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  0D 13 CR  ^M    Carriage-Return - Retorno do carro (enter)
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  0E 14 SO  ^N    Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.)
  stextonovo := TRANSLATE(stextonovo, '\15','');  --  0F 15 SI  ^O    Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.)
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  10 16 DLE ^P    Data-Link Escape
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  11 17 DC1 ^Q    Device-Control 1
  stextonovo := TRANSLATE(stextonovo, ' 8','');  --  12 18 DC2 ^R    Device-Control 2
  stextonovo := TRANSLATE(stextonovo, ' 9','');  --  13 19 DC3 ^S    Device-Control 3
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  14 20 DC4 ^T    Device-Control 4
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  15 21 NAK ^U    Neg-Acknowledge - Não-reconhecimento
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  16 22 SYN ^V    Synchronous Idle
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  17 23 vETB^W     End-of-Transmission Block
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  18 24 CAN ^X    Cancel
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  19 25 EM     ^Y  End-Of-Medium
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1A 26 SUB ^Z    Substitute
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1B 27 ESC ^[    Escape
  stextonovo := TRANSLATE(stextonovo, ' 8','');  --  1C 28 FS     ^\  File Separator
  stextonovo := TRANSLATE(stextonovo, ' 9','');  --  1D 29 GS  ^]    Group Separator
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1E 30 RS  ^^     Record Separator
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1F 31 US  ^_    Unit Separator
  stextonovo := TRANSLATE(stextonovo, '\127',''); --  7F127 DEL ^?     Delete

  RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

--SELECT 'SEM STRESS...   W TECLA ENTER TECLA ESC TESTE OK ' AS test1

--
-- Nome Artefato/Programa..: sp_comment_domain.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 31/08/2010
-- Data Atual..............: 31/08/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para fazer tratamento dos comentarios de dominio
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS sp_comment_domain (TEXT);
CREATE OR REPLACE FUNCTION sp_comment_domain (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo             TEXT;
  stexto_transitorio     TEXT;
  limite                 INTEGER;
BEGIN

     SELECT INTO limite array_upper(string_to_array(stexto, '§§'),1);

     IF limite > 1 THEN
   
          stextonovo := '';
        
          FOR i IN 2 .. limite LOOP
               SELECT INTO stexto_transitorio a.ar[i] FROM (SELECT string_to_array(stexto, '§§') AS ar ) AS a ;
               stextonovo  := stextonovo || stexto_transitorio || CHR(9);  --9 tabulacao horizontal ou 10 proxima linha ou 11 tabulacao vertical
          END LOOP;
        
     END IF;

     RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER;

/*

SELECT sp_comment_domain ('Status do bem referente a sua situação negocial. Representado pela classe de constantes: br.ufrn.sipac.patrimonio.tombamento.dominio.TipoStatus, podendo ter tais características:
§§ EFETIVADO = 1 (Quando o bem acaba de ser tombado, sua situação fica normal para realizar operações.),
§§ DEVOLVIDO = 2 (Para bens de terceiros, quando devolvido a entidade terceira que o cedeu),
§§PENDENTE = 3. (Bem terceiro não finalizado e que pertence à entidade que o adquiriu.),
§§ ALIENADO = 4 (Bem que sofreu processo de alienação),
§§ ACAUTELADO = 5 (Bem que sofreu processo de acautelamento),
§§ EM_PROCESSO_ALIENACAO = 6 (Bem que está em processo de alienação),
§§ EM_MOVIMENTACAO = 7 (Bem que está sob movimentação temporária).') AS test1

*/


--views deprecated
/*
DROP VIEW IF EXISTS view_close_db;
DROP VIEW IF EXISTS view_con;
DROP VIEW IF EXISTS view_disk_mem;
DROP VIEW IF EXISTS view_monitor;
DROP VIEW IF EXISTS view_monitor2;
DROP VIEW IF EXISTS view_size_db;
DROP VIEW IF EXISTS view_status_locks;
DROP VIEW IF EXISTS view_status_locks_tables;
DROP VIEW IF EXISTS view_time_server;
--view renomeada para vw_db_size;
DROP VIEW IF EXISTS vw_size_db;
--views criadas com dono errado
DROP VIEW IF EXISTS vw_close_db;
DROP VIEW IF EXISTS vw_con;
DROP VIEW IF EXISTS vw_disk_mem;
DROP VIEW IF EXISTS vw_find;
DROP VIEW IF EXISTS vw_monitor;
DROP VIEW IF EXISTS vw_size_db;
DROP VIEW IF EXISTS vw_status_locks;
DROP VIEW IF EXISTS vw_status_locks_tables;
DROP VIEW IF EXISTS vw_time_server;
--
*/

-- view lista status das tabelas relativo a locks
DROP VIEW IF EXISTS vw_status_locks;
CREATE OR REPLACE VIEW vw_status_locks AS
(
-- locks em andamento

      SELECT d.client_addr AS "maquina",
             c.datname AS "banco",
             e.schemaname AS "esquema",
             b.relname AS "tabela",
             a.mode,
             CASE
               WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela'
               WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela'
           WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO'
               WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela'
               WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela'
             END AS "modo"
        FROM pg_locks a
  INNER JOIN pg_class b
          ON b.oid = a.relation
         AND b.relkind = 'r'
  INNER JOIN pg_tables e
          ON b.relname = e.tablename
  INNER JOIN pg_database c
          ON c.oid = a.database
  INNER JOIN pg_stat_activity d
          ON d.procpid = a.pid
       WHERE relname NOT LIKE 'pg_%'
            --AND a.mode  = 'ExclusiveLock' -- Travamento da tabela
         --AND a.mode  = 'RowExclusiveLock' -- Travamento do registro da tabela
    ORDER BY modo DESC,
             --a.mode ASC,
             d.client_addr ASC,
             b.relname ASC
 )
;
COMMENT ON VIEW vw_status_locks IS '
Autor: O Peregrino
View que verifica situacao de locks em andamento no banco'
;


-- view que mostra locks em andamento de registros e tabelas
DROP VIEW IF EXISTS vw_status_locks_tables;
CREATE OR REPLACE VIEW vw_status_locks_tables AS
(
      SELECT d.client_addr AS "maquina",
             c.datname AS "banco",
             e.schemaname AS "esquema",
             b.relname AS "tabela",
             a.mode,
             CASE
               WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela'
               WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela'
           WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO'
               WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela'
               WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela'
             END AS "modo"
        FROM pg_locks a
  INNER JOIN pg_class b
          ON b.oid = a.relation
         AND b.relkind = 'r'
  INNER JOIN pg_tables e
          ON b.relname = e.tablename
  INNER JOIN pg_database c
          ON c.oid = a.database
  INNER JOIN pg_stat_activity d
          ON d.procpid = a.pid
       WHERE relname NOT LIKE 'pg_%'
        AND a.mode  = 'ExclusiveLock' -- Travamento da tabela
        AND a.mode  = 'RowExclusiveLock' -- Travamento do registro da tabela
    ORDER BY modo DESC,
             --a.mode ASC,
             d.client_addr ASC,
             b.relname ASC
 )
;
COMMENT ON VIEW vw_status_locks_tables IS '
Autor: O Peregrino
View que verifica situacao apenas de locks de registros e tabelas no banco'
;


--view para mostrar o uso de memoria disco do postgres
DROP VIEW IF EXISTS vw_disk_mem;
CREATE OR REPLACE VIEW vw_disk_mem AS
(
SELECT (SUM(pg_stat_database.blks_hit) / SUM(pg_stat_database.blks_read + pg_stat_database.blks_hit) * 100::NUMERIC)::INTEGER AS "% de Utilização de Mem"
  FROM pg_stat_database
)
;
COMMENT ON VIEW vw_disk_mem IS '
Autores: O Peregrino (apenas a view) Kenia Milene |http://keniamilene.wordpress.com| (query)
View que calcula através das estatisticas o percentual de utilização disco / memória.
Valores acima de 70% significa que o banco esta realizado mais tarefas em memória do que i/o em disco';


-- view para listar conexoes ativas e transações efetuadas (commit-rollback)
DROP VIEW IF EXISTS vw_con;     
CREATE OR REPLACE VIEW vw_con AS
(
   
     SELECT DISTINCT datname,
                     pg_stat_get_db_xact_commit(datid) AS "commits",
                     pg_stat_get_db_xact_rollback(datid) AS "rollbacks",
                     pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid) AS "transacoes_efetuadas",
                     CASE WHEN pg_stat_get_db_xact_commit(datid) = 0 THEN  -- evita divisao por zero
                       0
                     ELSE
                       (pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid))/(pg_stat_get_db_xact_commit(datid)/100)
                     END AS "%",
                     pg_stat_get_db_numbackends(datid) AS conexoes_ativas
                FROM pg_stat_activity
            ORDER BY 5 DESC
)
;
COMMENT ON VIEW vw_con IS '
Autor: O Peregrino
View que mostra conexoes ativas e transações efetuadas (commit-rollback)'
;


-- view para monitoramento de atividade do servidor postgres por usuario
DROP VIEW IF EXISTS vw_monitor;     
CREATE OR REPLACE VIEW  vw_monitor AS
(
         SELECT procpid,
            datname AS banco,
            usename AS usuario_banco,
            client_addr AS ip,
            CASE WHEN waiting IS TRUE THEN
              'Aguardando'
            ELSE
              'Executando'
            END AS status,
            AGE(now(),query_start) AS tempo_execucao,
          
            current_query
           FROM pg_stat_activity
          WHERE 1=1
              AND current_query <> '' -- DESOCUPADO
       ORDER BY tempo_execucao DESC
                --current_query DESC
               --client_addr ASC,
               --procpid ASC
)
;
COMMENT ON VIEW vw_monitor IS '
Autor: O Peregrino
View que verifica o que está executando no banco no instante atual incluido transacoes, listado  por usuario e ip e por tempo
Adequado para super-usuario
--SELECT *
   FROM vw_monitor;
OBS:
Para cancelar processo executar os comandos abaixos 
-- cancelar query >= 8.0
--SELECT pg_cancel_backend(21471);
-- cancelar processo >= 8.4
--SELECT pg_terminate_backend(21471);
'
;


/*
-- DESATIVADA POIS NECESSITA DE TABELAS CRIADA PELO DBA E REDE COM IP FIXOS
-- (DBA) - monitoramento de atividade do servidor postgres por usuario
DROP VIEW IF EXISTS vw_monitor2;
CREATE OR REPLACE VIEW vw_monitor2 AS
(
     SELECT a.procpid,
            a.datname AS banco,
            a.usename AS usuario_banco,
            a.client_addr AS ip,
            b.nome,
            b.talk,
            b.sistema,
            CASE WHEN a.waiting IS TRUE THEN
              'Aguardando'
            ELSE
              'Executando'
            END AS status,
            AGE(now(),a.query_start) AS tempo_execucao,
        a.current_query
       FROM pg_stat_activity a
  LEFT JOIN system.users b
         ON a.client_addr = TRIM(b.ip)::inet
      WHERE 1=1
      --AND a.usename = 'sipac'
      --AND a.datname LIKE 'administrativo%'
        AND a.current_query <> '' -- DESOCUPADO
      --AND a.usename = 'postgres'
      --AND a.client_addr = '10.3.128.136' -- Emerson
      --AND a.client_addr = '10.3.128.70' -- Itamir
   ORDER BY --a.current_query DESC,
            tempo_execucao DESC,
            b.nome ASC
            --a.client_addr ASC,
            --a.procpid ASC
)          
;

COMMENT ON VIEW vw_monitor2 IS '
Verifica o que está executando no banco no instante atual, incluindo transacoes, listado por usuario e ip e por tempo usando tabela system.users
Adequado para super-usuario
--SELECT *
   FROM vw_monitor;
OBS:
Para cancelar processo executar os comandos abaixos 
-- cancelar query >= 8.0
--SELECT pg_cancel_backend(21471);
-- cancelar processo >= 8.4
--SELECT pg_terminate_backend(21471);
'
;


-- estrutura de tabela a ser criada pelo DBA para funcionamento da view vw_monitor2

DROP SCHEMA IF EXISTS "system";
CREATE SCHEMA "system";
DROP TABLE IF EXISTS "system".users;
CREATE TABLE "system".users
(
  id           SERIAL                   NOT NULL,
  nome         CHARACTER VARYING(50)    NOT NULL,
  talk         CHARACTER VARYING(200),
  email        CHARACTER VARYING(200),
  ramal        CHARACTER VARYING(20),
  celular      CHARACTER VARYING(350),
  residencial  CHARACTER VARYING(350),
  computador   CHARACTER VARYING(200),
  ip           CHARACTER VARYING(200)   NOT NULL,
  sistema      CHARACTER VARYING(20),
  obs          TEXT,
  CONSTRAINT users_pkey PRIMARY KEY (id)
);


INSERT INTO "system".users (id, nome, talk, email, ramal, celular, residencial, computador, ip, sistema, obs)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?,  ?, ?, ?);

SELECT * FROM "system".users;

*/


-- view que mostra o tempo de execucao do servidor corrente
DROP VIEW IF EXISTS vw_time_server;
CREATE OR REPLACE VIEW vw_time_server AS
(

     -- quando o servidor do postgres foi iniciado, tempo?
     SELECT TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in,
            AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age
)
;
COMMENT ON VIEW vw_time_server IS '
Autor: O Peregrino
View que mostra quanto o servidor do postgres foi iniciado, tempo de execucao'
;


-- view que mostra tamanho dos bancos
DROP VIEW IF EXISTS vw_db_size;
CREATE OR REPLACE VIEW vw_db_size AS
(
     --tamanho dos bancos em MB
     SELECT datname AS banco,
            pg_database_size(datname)/1000000 || ' MB' AS tamanho_bd
       FROM pg_database
   ORDER BY tamanho_bd DESC
)
;  
COMMENT ON VIEW vw_db_size IS '
Autor: O Peregrino
View que mostra o tamanho dos bancos em MB'
;


-- view  que executa tentativa de fechamento de conexoes, necessario permissao de super-usuario,
-- em testes
DROP VIEW IF EXISTS vw_close_db;
CREATE OR REPLACE VIEW vw_close_db AS
(
     SELECT procpid
            , datname AS banco
            , client_addr AS ip
            , pg_cancel_backend(procpid) AS sit
       FROM pg_stat_activity
  
)
;
COMMENT ON VIEW vw_close_db IS '
Autor: O Peregrino
Derruba todos os usuario conectados.
Necessário permissao de super-usuario
OBS: Em testes'
;


-- view para descobrir esquema de uma tabela ou quais as tabelas existentes em um esquema por quantidade de registros
DROP VIEW IF EXISTS vw_find CASCADE;
CREATE OR REPLACE VIEW vw_find AS
(
     SELECT
            n.nspname AS esquema
            , 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
            , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_sem_indices
            , pg_size_pretty(pg_total_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_com_indices             
       FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n
         ON n.oid = c.relnamespace
      WHERE c.relkind = 'r'
        AND n.nspname NOT IN (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'                                
                             )
        AND n.nspname NOT ILIKE 'pg_temp_%'
        AND n.nspname NOT ILIKE 'pg_ts_%'
        AND c.relname NOT ILIKE 'pg_ts_%'
   ORDER BY 1 ASC

)
; 
COMMENT ON VIEW vw_find IS '
Autor: O Peregrino
View que procura por tabelas, esquemas, comentários, donos no banco
--exemplos:

--busca simples (tudo)
     SELECT *
       FROM vw_find
     ;

--busca por esquema
     SELECT *
       FROM vw_find
      WHERE esquema ILIKE \\\'esquema\\\'
      ;

--busca por tabelas
     SELECT *
       FROM vw_find
      WHERE tabela ILIKE \\\'%tabela%\\\'
      ;
     
'
;
/*
--exemplos de vw_find:
--busca simples (tudo)
     SELECT *
       FROM vw_find
     ;
--busca por esquema
     SELECT *
       FROM vw_find
      WHERE esquema ILIKE 'esquema'
      ;
--busca por tabelas
     SELECT *
       FROM vw_find
      WHERE tabela ILIKE '%tabela%'
      ;
     
*/


-- view para procuar as sequencias
DROP VIEW IF EXISTS vw_find_seq;
CREATE OR REPLACE VIEW vw_find_seq AS
(
     SELECT n.nspname AS esquema,
            c.relname AS seq,
            pg_catalog.pg_get_userbyid(c.relowner) AS dono,
            pg_catalog.obj_description(c.oid, 'pg_class') AS comentario,
            reltuples::integer AS registros
       FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n
         ON n.oid = c.relnamespace
      WHERE c.relkind = 'S' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table
        AND n.nspname NOT IN (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
   ORDER BY n.nspname,
            c.relname

);
COMMENT ON VIEW vw_find_seq IS '
Autor: O Peregrino
View que procura por esquemas, sequencia, comentários, donos no banco
--exemplos:

--busca simples (tudo)
     SELECT *
       FROM vw_find_seq
     ;

--busca por esquema
     SELECT *
       FROM vw_find_seq
      WHERE esquema ILIKE \\\'esquema\\\'
      ;

--busca por tabelas
     SELECT *
       FROM vw_find_seq
      WHERE seq ILIKE \\\'%seq%\\\'
      ;
     
'
;

-- view para listar todas a views de usuario
DROP VIEW IF EXISTS vw_list_views;
CREATE OR REPLACE VIEW vw_list_views AS
(
     SELECT *
       FROM  pg_views
       WHERE schemaname NOT IN (
                                'pg_catalog',
                                'information_schema',
                                'pg_toast'
                               )
);
COMMENT ON VIEW vw_list_views IS '
Autor: O Peregrino
View que lista todas as views dos usuarios no banco
--SELECT * FROM vw_list_views;'
;


-- view que lista todas as colunas de todas as tabelas
DROP VIEW IF EXISTS vw_find_attrib CASCADE;
CREATE OR REPLACE VIEW vw_find_attrib AS
(
     SELECT
             b.esquema
           , b.tabela
           , b.oid_tabela
           , b.registros
           , a.attname AS "nome_atributo"
           , pg_catalog.format_type(a.atttypid, a.atttypmod) AS "formato_tipo_atributo"
           , a.attnotnull AS "nao_nulo"
           , a.attnum AS "ordem_atributo_tabela"
           , pg_catalog.col_description(b.oid_tabela, a.attnum) AS "comentario_coluna"
           , (
               SELECT SUBSTRING(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128)
                 FROM pg_catalog.pg_attrdef d
                WHERE d.adrelid = a.attrelid
                  AND d.adnum = a.attnum
                  AND a.atthasdef
             ) AS padrao
       FROM (
             SELECT c.oid AS "oid_tabela",
                    c.relname AS "tabela",    
                    n.nspname AS "esquema",
                    reltuples::integer AS "registros"
               FROM pg_catalog.pg_class c
          LEFT JOIN pg_catalog.pg_namespace n
                 ON n.oid = c.relnamespace
              WHERE c.relkind = 'r' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table
                AND n.nspname NOT IN
                             (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
           ORDER BY reltuples::integer DESC               
           ) AS b
 INNER JOIN pg_catalog.pg_attribute a
         ON a.attrelid = b.oid_tabela        
      WHERE 1=1
        AND a.attnum > 0 -- despreza 'oid','cmax','xmax','cmin','xmin','ctid','tableoid'
        AND NOT a.attisdropped  -- colunas excluidas
   ORDER BY b.registros DESC,
            b.esquema ASC, 
            b.oid_tabela ASC,
            ordem_atributo_tabela ASC
)
;
COMMENT ON VIEW vw_find_attrib IS '
Autor: O Peregrino
View que procura por nome_atributo, formato_tipo_atributo, etc.
--SELECT * FROM vw_find_attrib WHERE comentario_coluna IS NULL;'
;  


-- view para listar os bancos liberados ou nao para acesso
DROP VIEW IF EXISTS vw_db_list_access;
CREATE OR REPLACE VIEW vw_db_list_access AS
(
           SELECT a.datname AS banco,
                  b.usename AS dba,
                  CASE WHEN a.datallowconn = TRUE THEN
                    'LIBERADO ACESSO'
                  ELSE
                    'BLOQUEADO ACESSO'
                  END AS situacao
             FROM pg_database a
       INNER JOIN pg_user b
               ON b.usesysid = a.datdba
);

COMMENT ON VIEW vw_db_list_access IS '
Autor: O Peregrino
View que lista bancos com acesso liberados ou bloqueados.
--OBS: Apenas super-usuario pode executar as clausulas abaixo
--Para bloquear um banco
--ALTER DATABASE foo CONNECTION LIMIT 0; --(so super-usuários podem se conectar)
--Para desbloquear um banco
--ALTER DATABASE foo CONNECTION LIMIT -1; --(so super-usuários podem se conectar)
--SELECT * FROM vw_db_list_access;
'
;  


-- view que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido
DROP VIEW IF EXISTS vw_table_relat;
CREATE OR REPLACE VIEW vw_table_relat AS
(
     SELECT c.oid,
            n.nspname AS esquema,
            c.relname AS tabela,
            conname,
            consrc
       FROM (
             SELECT conname,
                    pg_catalog.pg_get_constraintdef(oid) AS consrc,
                    conrelid as relid
               FROM pg_catalog.pg_constraint
              WHERE contype = 'f'
            ) AS subq
       JOIN pg_catalog.pg_class c ON relid = c.oid
       JOIN pg_namespace AS n ON n.oid = c.relnamespace
);

COMMENT ON VIEW vw_table_relat IS '
Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query)
View que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido
--SELECT * FROM vw_table_relat;'
;  



--view que mostra as tabelas que estao com tuplas mortas e precisam de vacuum
DROP VIEW IF EXISTS vw_dead_tuples;
CREATE OR REPLACE VIEW vw_dead_tuples AS
(

     SELECT *
       FROM pg_stat_all_tables
      WHERE n_dead_tup > 0
   ORDER BY n_dead_tup DESC
)
;

COMMENT ON VIEW vw_dead_tuples IS '
Autor: O Peregrino
View que retorna no nome das tabelas que estao com tuplas mortas e precisam de vacuum
--SELECT * FROM vw_dead_tuples;'
;  


-- view que mostrar quantidade de usuario conectados no banco
DROP VIEW IF EXISTS vw_db_users_number;
CREATE OR REPLACE VIEW vw_db_users_number AS
(

    SELECT COUNT(*) AS total
      FROM (
             SELECT
           DISTINCT client_addr
               FROM pg_stat_activity
            ) AS x
);

COMMENT ON VIEW vw_db_users_number IS '
Autor: O Peregrino
View que retorna quantos usuarios conectados ao banco
--SELECT * FROM vw_db_users_number;
'
;  


--view para identificação de índices duplicados
DROP VIEW IF EXISTS vw_idx_duplicate;
CREATE OR REPLACE VIEW vw_idx_duplicate AS
(
        SELECT pg_stat_user_indexes.schemaname AS esquema
             , pg_stat_user_indexes.relname AS tabela
             , pg_attribute.attname AS nome_atributo
             , pg_stat_user_indexes.indexrelname AS nome_indice
             , CASE pg_index.indisprimary WHEN 't' THEN
                 'Sim'
                  ELSE
                 'Nao'
               END
            AS indice_na_chave_primaria
          FROM pg_index
          JOIN pg_stat_user_indexes USING (indexrelid)
          JOIN
               (
                SELECT pg_index.indrelid,
                       pg_index.indkey,
                       count(*)
                  FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid)
              GROUP BY pg_index.indrelid
                       , pg_index.indkey
                HAVING count(*)>1
                ) ind_dup
             ON pg_index.indrelid=ind_dup.indrelid
                AND pg_index.indkey=ind_dup.indkey
           JOIN pg_attribute
             ON pg_attribute.attrelid=ind_dup.indrelid
                AND pg_attribute.attnum=SOME(pg_index.indkey)
       ORDER BY pg_stat_user_indexes.schemaname
              , pg_stat_user_indexes.relname
              , pg_index.indisprimary='t' DESC
);
COMMENT ON VIEW vw_idx_duplicate IS '
Autores: O Peregrino (apenas a view) JotaComm |http://jotacomm.wordpress.com| (query)
View que retorna identificação de índices duplicados no banco
--SELECT * FROM vw_idx_duplicate;'
;  


--view que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique)
DROP VIEW IF EXISTS vw_list_unique CASCADE;
CREATE OR REPLACE VIEW vw_list_unique AS
(
    SELECT
   DISTINCT
            x.nome_constraint
          , x.esquema
          , c2.relname AS tabela
          , x.coluna AS nome_atributo         
       FROM
            (
               SELECT           
                      n.nspname AS esquema
                    , c.relname AS nome_constraint
                    , at.attname AS coluna
                    , cn.conrelid AS oid
                 FROM pg_catalog.pg_constraint cn
           INNER JOIN pg_catalog.pg_index i
                   ON (cn.conrelid = i.indrelid)
           INNER JOIN pg_catalog.pg_attribute at
                   ON at.attrelid = i.indexrelid
           INNER JOIN pg_catalog.pg_class c
                   ON c.oid = at.attrelid
            LEFT JOIN pg_catalog.pg_namespace n
                   ON n.oid = c.relnamespace              
                WHERE 1=1
                  AND i.indisunique IS TRUE
                  AND i.indisprimary IS FALSE
                  AND cn.contype = 'u' --UNIQUE
             ) AS x
  INNER JOIN pg_catalog.pg_class c2
          ON c2.oid = x.oid
       WHERE 1=1
)
;      
COMMENT ON VIEW vw_list_unique IS '
Autor: O Peregrino
View que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique)
Exemplo: CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor
--SELECT * FROM vw_list_unique;
--Exemplo:
--CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor'
;  


--view que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check)
-- em teste
DROP VIEW IF EXISTS vw_list_check CASCADE;
CREATE OR REPLACE VIEW vw_list_check AS
(
     SELECT
   DISTINCT
            x.nome_constraint
          , x.esquema
          , c2.relname AS tabela
          , x.coluna AS nome_atributo
          , x.conteudo         
       FROM
            (
               SELECT           
                      n.nspname AS esquema
                    , c.relname AS nome_constraint
                    , at.attname AS coluna
                    , cn.conrelid AS oid
                    , cn.consrc AS conteudo
                 FROM pg_catalog.pg_constraint cn
           INNER JOIN pg_catalog.pg_index i
                   ON (cn.conrelid = i.indrelid)
           INNER JOIN pg_catalog.pg_attribute at
                   ON at.attrelid = i.indexrelid
           INNER JOIN pg_catalog.pg_class c
                   ON c.oid = at.attrelid
            LEFT JOIN pg_catalog.pg_namespace n
                   ON n.oid = c.relnamespace              
                WHERE 1=1
                  AND cn.contype = 'c' --CHECK
             ) AS x
  INNER JOIN pg_catalog.pg_class c2
          ON c2.oid = x.oid
       WHERE 1=1 -- comentar essa linha se quiser listar todos
       
);
COMMENT ON VIEW vw_list_check IS '
Autor: O Peregrino
View que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check)
OBS: Em testes
--SELECT * FROM vw_list_check;  '
;  


-- view que lista todas as chaves primarias (pk) de todas as tabelas
DROP VIEW IF EXISTS vw_list_pk CASCADE;
CREATE OR REPLACE VIEW vw_list_pk AS
(
     SELECT  d.esquema
          ,  b.relname AS tabela
          ,  c.attname AS nome_atributo
          ,  pg_catalog.format_type(c.atttypid, c.atttypmod) AS formato_tipo_atributo
          ,  pg_catalog.col_description(d.oid_tabela, c.attnum) AS comentario_coluna
          ,  c.attnum AS ordem_atributo_tabela
          ,  d.registros
       FROM (
             SELECT c.oid AS "oid_tabela",
                    c.relname AS "tabela",    
                    n.nspname AS "esquema",
                    reltuples::integer AS "registros"
               FROM pg_catalog.pg_class c
          LEFT JOIN pg_catalog.pg_namespace n
                 ON n.oid = c.relnamespace
              WHERE c.relkind = 'r'            
                AND n.nspname NOT IN
                             (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
           ORDER BY reltuples::integer DESC                          
             ) AS d
  INNER JOIN pg_index a
          ON a.indrelid = d.oid_tabela
  INNER JOIN pg_class b
          ON b.oid = a.indrelid
  INNER JOIN pg_attribute c
          ON c.attrelid = b.oid
      WHERE
             b.oid IN (
                       SELECT oid
                         FROM pg_catalog.pg_class
                        WHERE relkind = 'r'
                          AND relname NOT LIKE 'pg_%'
                          AND relname NOT LIKE 'sql_%'
                       )
       AND c.attnum = ANY(a.indkey)
       AND a.indisprimary IS TRUE
       AND a.indisunique IS TRUE
       AND c.attnotnull IS TRUE
       AND c.attrelid = b.oid
       --AND c.attnum > 1
  ORDER BY 1 ASC,
           2 ASC
);
COMMENT ON VIEW vw_list_pk IS '
Autores: O Peregrino / José Flávio DBA |zflavio at gmail.com|
View que retorna uma lista das chaves primarias (pk) do banco
--SELECT * FROM vw_list_pk;
'
;  

--View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados
DROP VIEW IF EXISTS vw_max_id CASCADE;
CREATE OR REPLACE VIEW vw_max_id AS
(
               (
               SELECT 'SELECT '
                   || '\''||x.esquema||'.'||x.tabela||'\''
                   || ' AS esquema_tabela'
                   || ','
                   || ' (SELECT MAX('
                   || x.nome_atributo
                   || ')'
                   || ' FROM '
                   || x.esquema
                   || '.'
                   || '\"'||x.tabela||'\"'
                   || ')::BIGINT AS maior_id UNION '
                   AS maior_ids
                 FROM (
                       SELECT *
                         FROM vw_list_pk
                        WHERE esquema
                       NOT IN ('migracoes'
                               ,'z_deprecated'
                               ,'complexo_hospitalar'
                               ,'espaco_fisico'
                              )
                          AND formato_tipo_atributo NOT IN ('date')
                          AND formato_tipo_atributo NOT ILIKE 'character%'
                          AND tabela NOT ILIKE '@%'
                     ORDER BY 7 DESC
                       ) AS x
               )
         UNION
               (
                 SELECT 'SELECT \'ZZZ-FIM-USADO-PARA-CONTROLE\' AS esquema_tabela,  0::BIGINT AS maior_id ORDER BY 2 DESC;' AS maior_ids
               )
        ORDER BY 1 ASC
);
COMMENT ON VIEW vw_max_id IS '
Autor: O Peregrino
View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados
-- O resultado desta view gera um nova query para obter o resultado
--  desejado.
-- Esta consulta pega o maior id de todas tabelas de um banco.
-- É gerado apenas um coluna como resultado da view com query.
--  para obter o valor maximo de um id em uma tabela, de todas as tabelas de um  banco.
-- Dica para execução:
-- No pgadmin selecione CTRL + A, CTRL + C no resultado da query,
--  Abrir uma nova query, CTRL + V,  CTRL + HOME,
--  CTRL + F, substituir " por nada, recomendado, em vez de " ser |, se estiver configurado
--  Clicar em (Substituir TODOS) e depois em (Fechar)
--  Executar query (f5)
-- Será retornada 2 colunas: esquema_tabela e o maior_id
-- Essa view vw_max_id usa a view vw_list_pk
--SELECT * FROM vw_max_id;
'
;  




-- view para checar compatibilidade de tipo dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos compativeis
DROP VIEW IF EXISTS vw_list_type_not_compatible_relat;
CREATE OR REPLACE VIEW vw_list_type_not_compatible_relat AS
(
SELECT tb_virtual.oid_fk
     , tb_virtual.esquema_fk
     , tb_virtual.tabela_fk
     , tb_virtual.coluna_fk
     , tb_virtual.oid_referenciada
     , tb_virtual.esquema_tabela_referenciada
     , tb_virtual.coluna_referenciada
     , tb_virtual.nome_constraint
     , tb_virtual.sql_constraint
     , pg_catalog.format_type(at.atttypid, at.atttypmod) AS formato_tipo_coluna_referenciada
     , tb_virtual.formato_tipo_coluna_fk
  FROM
      (

          SELECT c.oid AS oid_fk
               , n.nspname AS esquema_fk
               , c.relname AS tabela_fk
               , SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME AS coluna_fk
               , subq.confrelid AS oid_referenciada
               , SUBSTRING(subq.consrc,'REFERENCES (.*)\\(')::NAME AS esquema_tabela_referenciada
               , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME AS coluna_referenciada
               , pg_catalog.format_type(a.atttypid, a.atttypmod) AS formato_tipo_coluna_fk
               , subq.conname AS nome_constraint
               , subq.consrc AS sql_constraint
            FROM (
                     SELECT c.conname
                          , pg_catalog.pg_get_constraintdef(oid) AS consrc
                          , c.conrelid AS relid
                          , c.confrelid
                       FROM pg_catalog.pg_constraint c
                      WHERE c.contype = 'f'
                 ) AS subq
             JOIN pg_catalog.pg_class c
               ON relid = c.oid
             JOIN pg_namespace AS n
               ON n.oid = c.relnamespace
             JOIN pg_catalog.pg_attribute a
               ON a.attrelid = c.oid
             JOIN pg_type AS t
               ON t.oid = a.atttypid
            WHERE 1=1
              AND a.attname IN (
                                  SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME -- coluna_fk
                                , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME  -- coluna_referenciada
                               )
      ) AS tb_virtual
INNER JOIN pg_catalog.pg_attribute AS at
        ON tb_virtual.oid_referenciada = attrelid
      JOIN pg_type AS tt
       ON tt.oid = at.atttypid
     WHERE attname = tb_virtual.coluna_fk
       AND  tb_virtual.formato_tipo_coluna_fk <> pg_catalog.format_type(at.atttypid, at.atttypmod) -- formato_tipo_coluna_referenciada
  ORDER BY 1 ASC
);

COMMENT ON VIEW vw_list_type_not_compatible_relat IS '
Autor: O Peregrino
View que retorna uma lista de tipos dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos nao compativeis
--SELECT * FROM vw_list_type_not_compatible_relat;  '
;  


-- View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos  correntes no servidor
DROP VIEW IF EXISTS vw_info_utils;
CREATE OR REPLACE VIEW vw_info_utils AS
(
     SELECT TO_CHAR(NOW(),'DD/MM/YYYY HH:MI:SS') AS agora
          , SESSION_USER AS usuario_corrente
          , CURRENT_SCHEMA() AS esquema_corrente
          , CURRENT_DATABASE() AS banco_corrente
          , INET_CLIENT_ADDR() AS ip_cliente
          , INET_CLIENT_PORT() AS porta_cliente
          , INET_SERVER_ADDR() AS ip_servidor
          , INET_SERVER_PORT() AS porta_servidor
          , TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in
          , AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age
          , VERSION() AS versao_postgres         
          , PG_BACKEND_PID() AS pid_corrente
          , (SELECT COUNT(*) FROM pg_stat_get_backend_idset()) AS qt_pid_processos_correntes_servidor
)
;
COMMENT ON VIEW vw_info_utils IS '
Autor: O Peregrino
View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos  correntes no servidor
--SELECT * FROM vw_info_utils;'
;


-- view que mostra data do ultimo vacuum
DROP VIEW IF EXISTS vw_list_last_vacuum;
CREATE OR REPLACE VIEW vw_list_last_vacuum AS
(
     SELECT schemaname AS esquema
          , relname AS tabela
          , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
          , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
       FROM pg_stat_all_tables
)
;
COMMENT ON VIEW  vw_list_last_vacuum IS '
Autor: O Peregrino
View que retorna uma lista de tabelas com o data do ultimo vacuum
--SELECT * FROM vw_list_last_vacuum;'
;


-- lista funcoes e esquema
DROP VIEW IF EXISTS vw_list_user_functions;
CREATE OR REPLACE VIEW vw_list_user_functions AS
(
     SELECT a.routine_catalog
          , a.routine_schema
          , a.routine_name
          , a.routine_type
          , b.prosrc
       FROM information_schema.routines a
 INNER JOIN pg_proc b
         ON a.routine_name = b.proname
      WHERE 1=1
        AND a.routine_schema NOT IN (
                                         'pg_catalog'
                                         ,'information_schema'
                                         ,'pg_toast'
                                    )
)
;
COMMENT ON VIEW  vw_list_user_functions IS '
Autor: O Peregrino
View que retorna uma lista funcoes por esquema
--SELECT * FROM vw_list_user_functions;'
;


-- view que lista as tablespaces criadas
DROP VIEW IF EXISTS vw_list_tablespaces;
CREATE OR REPLACE VIEW vw_list_tablespaces AS
(
     SELECT spcname
          , pg_catalog.pg_get_userbyid(spcowner) AS spcowner
          , spclocation
       FROM pg_catalog.pg_tablespace
);

COMMENT ON VIEW vw_list_tablespaces IS '
Autor: O Peregrino
View que retorna uma lista das tablespaces criadas
--SELECT * FROM vw_list_tablespaces'
;


-- view que lista os indices ajuda a descobrir os indices de um esquema e ou tabela
DROP VIEW IF EXISTS vw_list_idx;
CREATE OR REPLACE VIEW vw_list_idx AS
(
     SELECT n.nspname AS esquema
          , c.relname AS tabela
          , pg_catalog.pg_get_userbyid(c.relowner) AS dono_tabela
          , c2.relname AS nome_indice
          , CASE WHEN i.indisprimary = TRUE THEN
              'SIM'
            ELSE
              'NAO'
            END AS primary_key
          , CASE WHEN i.indisunique = TRUE THEN
              'SIM'
            ELSE
              'NAO'
            END AS unique
          , i.indisclustered
          , i.indisvalid
          , pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)
        --, c2.reltablespace
          , pg_catalog.obj_description(c2.oid, 'pg_class') AS comentario_indice
          , c.reltuples::integer AS registros_tabela
          , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_indice
       FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n
         ON n.oid = c.relnamespace,
            pg_catalog.pg_class c2,
            pg_catalog.pg_index i
      WHERE 1=1
        AND c.relkind = 'r'
        AND n.nspname NOT IN (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
        AND c.oid = i.indrelid
        AND i.indexrelid = c2.oid
        AND n.nspname NOT ILIKE 'pg_temp_%'
        AND n.nspname NOT ILIKE 'pg_ts_%'
        AND c.relname NOT ILIKE 'pg_ts_%'
   ORDER BY n.nspname ASC
          , c.relname ASC
          , i.indisprimary DESC
          , i.indisunique DESC
          , c2.relname ASC                             
)
; 
COMMENT ON VIEW vw_list_idx IS '
Autor: O Peregrino
View que lista os indices, isto e, descobrir os indices de um esquema e ou tabela
--SELECT * FROM vw_list_idx;'
;


-- view que lista regras
DROP VIEW IF EXISTS vw_rules;
CREATE OR REPLACE VIEW vw_rules AS
(
     SELECT *
       FROM pg_rules
);
COMMENT ON VIEW vw_rules IS '        
Autor: O Peregrino
View que lista rules
--SELECT * FROM vw_rules;'
;


---view que mostra qual o limite para estouro maximo de transacoes (2 bilhoes)
DROP VIEW IF EXISTS vw_max_burst_transactions;
CREATE OR REPLACE VIEW vw_max_burst_transactions AS
(
     SELECT datname AS banco
          , AGE(datfrozenxid) AS transacoes
          , 2000000000 AS nao_ultrapassar_esse_limite
       FROM pg_database
);
COMMENT ON VIEW vw_max_burst_transactions IS '        
Autor: O Peregrino, baseado na documentação do PostgreSQL 8.0
View que mostra a quantidade de transacoes por banco/servidor realizadas.
OBS: O DBA NAO PODE DEIXAR ULTRAPASSAR 2 BILHOES TRANSACOES!!!
--SELECT * FROM vw_max_burst_transactions;'
;


-- view para tamanho do bloco no disco
DROP VIEW IF EXISTS vw_db_size_block;
CREATE OR REPLACE VIEW vw_db_size_block AS
(
     SELECT *
       FROM pg_settings
      WHERE name='block_size'
);
COMMENT ON VIEW vw_db_size_block IS '
Autor: O Peregrino
View que mostra o tamanho do block no disco
--SELECT * FROM vw_db_size_block;'
;


-- view que mostra o nivel de isolamento de transacoes
DROP VIEW IF EXISTS vw_isolation_level;
CREATE OR REPLACE VIEW vw_isolation_level AS
(
 SELECT name AS isolamento_transacao,
       setting AS configurado
  FROM pg_settings
 WHERE name LIKE 'transaction%'
);
COMMENT ON VIEW vw_isolation_level IS '
Autor: O Peregrino
View que mostra o nivel de isolamento de transacao
--SELECT * FROM vw_isolation_level;'
;


-- view que mostra as dependencias entre tabelas
DROP VIEW IF EXISTS vw_depend_tables;
CREATE OR REPLACE VIEW vw_depend_tables AS
(
     SELECT
            esq_pai.nspname AS esquema_pai   
          , tab_pai.relname AS tabela_pai
          , esq_filho.nspname AS esquema_filho
          , tab_filho.relname AS tabela_filho
          , esq_pai.nspname ||'.'|| tab_pai.relname AS esquema_tabela_pai
          , esq_filho.nspname || '.' || tab_filho.relname AS esquema_tabela_filho
       FROM pg_class AS tab_pai
 INNER JOIN pg_constraint
         ON tab_pai.oid = pg_constraint.confrelid
 INNER JOIN pg_class AS tab_filho
         ON pg_constraint.conrelid = tab_filho.oid
  LEFT JOIN pg_catalog.pg_namespace esq_pai
         ON esq_pai.oid = tab_pai.relnamespace       
  LEFT JOIN pg_catalog.pg_namespace esq_filho
         ON esq_filho.oid = tab_filho.relnamespace           
      WHERE 1=1
        AND tab_pai.relkind = 'r'
   ORDER BY esq_pai.nspname ASC,
            tab_pai.oid ASC  
);
COMMENT ON VIEW vw_depend_tables IS '
Autor: O Peregrino
View que mostra as dependencias entre as tabelas
--SELECT * FROM vw_depend_tables;'
;


--view que mostra as chaves estrangeiras, constraints
DROP VIEW IF EXISTS vw_list_fk CASCADE;
CREATE OR REPLACE VIEW vw_list_fk AS
(
     SELECT
            c.oid
          , n.nspname AS esquema
          , c.relname AS tabela
          , SUBSTRING(consrc,'\\((.*?)\\)') AS nome_atributo
          , SUBSTRING(consrc,'\REFERENCES (.*?)\\(') AS nome_esquema_tabela_referencia
          , conname AS nome_constraint
          , consrc AS conteudo_constraint
       FROM
            (
              SELECT conname,
                     pg_catalog.pg_get_constraintdef(oid) AS consrc,
                     conrelid as relid
                FROM pg_catalog.pg_constraint
               WHERE contype = 'f'
               --AND pg_catalog.pg_get_constraintdef(oid) ILIKE '%tabela%'
            ) AS subq
       JOIN pg_catalog.pg_class c
         ON relid = c.oid
       JOIN pg_namespace AS n
         ON n.oid = c.relnamespace       
);
COMMENT ON VIEW vw_list_fk IS '
Autor: O Peregrino
View que mostra as chaves estrangeiras, constraints das tabelas
--SELECT * FROM vw_list_fk;'
;

--view que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela
DROP VIEW IF EXISTS vw_find_pk_not_first;
CREATE OR REPLACE VIEW vw_find_pk_not_first AS
(
     SELECT
            nspname AS esquema
          , relname AS tabela
          , pg_attribute.attname AS nome_atributo
          , attnum AS ordem_atributo_tabela
       FROM pg_index
          , pg_class
          , pg_attribute
          , pg_namespace
      WHERE pg_namespace.oid = pg_class.relnamespace
        AND indrelid = pg_class.oid
        AND pg_attribute.attrelid = pg_class.oid
        AND pg_attribute.attnum = ANY(pg_index.indkey)
        AND indisprimary
        AND attrelid = pg_class.oid
        AND attnum > 1
        AND pg_class.oid IN (
                             SELECT oid
                               FROM pg_catalog.pg_class
                              WHERE relkind = 'r'
                                AND nspname NOT IN (
                                                         'pg_catalog'
                                                       , 'information_schema'
                                                       , 'pg_toast'
                                                   )
                                                 
                            )
     ORDER BY esquema ASC
            , tabela ASC

);
COMMENT ON VIEW vw_find_pk_not_first IS '
Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query)
View que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela
--SELECT * FROM vw_find_pk_not_first;'
;


--view que efetua a mesma visualizacao de view vw_find  com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco
DROP VIEW IF EXISTS vw_find_dml;
CREATE OR REPLACE VIEW vw_find_dml AS
(
     SELECT   x.esquema
            , x.tabela
            , x.dono
            , x.comentario
            , x.registros
            , x.tamanho_sem_indices
            , x.tamanho_com_indices
            , x.total_de_leituras_sequencial
            , x.total_de_leituras_indexada
            , x.total_de_leituras
            , x.total_de_inserts
            , x.total_de_updates
            , x.total_de_deletes
       FROM (
                 SELECT *
                   FROM vw_find
             INNER JOIN
                   (
                     SELECT relname AS tabelax
                          , SUM(seq_scan) AS total_de_leituras_sequencial
                          , SUM(idx_scan) AS total_de_leituras_indexada
                          , SUM(seq_scan+idx_scan) AS total_de_leituras
                          , SUM(n_tup_ins) AS total_de_inserts
                          , SUM(n_tup_upd) AS total_de_updates
                          , SUM(n_tup_del) AS total_de_deletes
                       FROM pg_stat_user_tables
                   GROUP BY relname
                   ) AS psut
                ON psut.tabelax = vw_find.tabela
            ) AS x
);
COMMENT ON VIEW vw_find_dml IS '
Autor: O Peregrino
View que efetua a mesma visualizacao de view vw_find  com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco
--SELECT * FROM vw_find_dml;'
;

--view que mostra os dominios
DROP VIEW IF EXISTS vw_list_domain CASCADE;
CREATE OR REPLACE VIEW vw_list_domain AS
(
SELECT n.nspname as esquema
     , t.typname as nome_dominio
     , pg_catalog.format_type(t.typbasetype, t.typtypmod) as formato_tipo_dominio,
       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault
            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'
            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault
            ELSE ''
       END as modifier,
       pg_catalog.array_to_string(ARRAY(
         SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid
       ), ' ') as check
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'd'
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2
);
COMMENT ON VIEW vw_list_domain IS '
Autor: O Peregrino
View que mostra os dominios cadastrados no banco
SELECT * FROM vw_list_domain;
';

DROP VIEW IF EXISTS vw_list_dict_data CASCADE;
CREATE OR REPLACE VIEW vw_list_dict_data AS
(
     SELECT
            at.esquema
          , at.tabela
          , at.esquema
            || '.'
            || at.tabela AS esquema_tabela
          , at.ordem_atributo_tabela
          , at.nome_atributo
          , at.formato_tipo_atributo
          , at.nao_nulo 
          , at.comentario_coluna
          , CASE WHEN (fk.nome_esquema_tabela_referencia IS NOT NULL) THEN
                    'TABELA: ' ||  fk.nome_esquema_tabela_referencia
                 WHEN (dn.nome_dominio IS NOT NULL) THEN
                    'LISTA: ' || dn.modifier || dn.check
            END AS dominio
          , CASE WHEN (pk.nome_atributo IS NOT NULL) THEN
               'X'
            END AS pri
          , CASE WHEN (fk.nome_atributo IS NOT NULL) THEN
               'X'
            END AS est         
          , CASE WHEN (unq.nome_atributo IS NOT NULL) THEN
               'X'
            END AS can
          , at.padrao
          , ck.conteudo AS check          
       FROM vw_find_attrib at
  LEFT JOIN vw_list_pk pk
         ON (
             at.esquema = pk.esquema
             AND at.tabela = pk.tabela
             AND at.nome_atributo = pk.nome_atributo
            )
  LEFT JOIN vw_list_check ck
         ON (
             at.esquema = ck.esquema
             AND at.tabela = ck.tabela
             AND at.nome_atributo = ck.nome_atributo
            )
  LEFT JOIN vw_list_unique unq
         ON (
             at.esquema = ck.esquema
             AND at.tabela = unq.tabela
             AND at.nome_atributo = unq.nome_atributo
            )         
  LEFT JOIN vw_list_fk fk
         ON (
             at.esquema = fk.esquema
             AND at.tabela = fk.tabela
             AND at.nome_atributo = fk.nome_atributo
            )         
  LEFT JOIN vw_list_domain dn
         ON ( at.formato_tipo_atributo  = dn.nome_dominio )
      WHERE 1=1
   ORDER BY at.esquema ASC
          , at.tabela ASC
          , at.ordem_atributo_tabela ASC
);
COMMENT ON VIEW vw_list_dict_data IS '
Autor: O Peregrino
View que lista o dicionario de dados de toda a base de dados
SELECT * FROM vw_list_dict_data;
';


DROP VIEW IF EXISTS vw_list_dict_data_docx CASCADE;
CREATE OR REPLACE VIEW vw_list_dict_data_docx AS
(
     SELECT
            esquema
          , tabela
          , esquema_tabela AS "Esquema.Tabela"
          , ordem_atributo_tabela
          , nome_atributo AS "Atributo"
          , CASE WHEN (formato_tipo_atributo = 'integer') THEN
                    'inteiro'
                 WHEN (formato_tipo_atributo = 'smallint') THEN
                    'inteiro_pesqueno'
                 WHEN (formato_tipo_atributo = 'bigint') THEN   
                    'inteiro_grande'
                 WHEN (formato_tipo_atributo = 'numeric') THEN                   
                    'numerico'
                 WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN
                    'numerico' || SUBSTRING(formato_tipo_atributo FROM 8)              
                 WHEN (formato_tipo_atributo = 'real') THEN
                    'real'
                 WHEN (formato_tipo_atributo = 'double precision') THEN
                    'dapla_precisao'                                      
                 WHEN (formato_tipo_atributo = 'date') THEN
                    'data'                  
                 WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN
                    'data_hora'
                 WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN
                    'data_hora'  
                 WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN
                    'data_hora'
                 WHEN (formato_tipo_atributo = 'time without time zone') THEN
                    'hora'
                 WHEN (formato_tipo_atributo = 'time with time zone') THEN
                    'hora'                  
                 WHEN (formato_tipo_atributo = 'text') THEN
                    'texto'
                 WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN
                    'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18)
                 WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN
                    'caracter' || SUBSTRING(formato_tipo_atributo FROM 10)                  
                 WHEN (formato_tipo_atributo = '"char"') THEN
                    'caracter'
                 WHEN (formato_tipo_atributo = 'bpchar') THEN
                    'caracter'
                 WHEN (formato_tipo_atributo = 'boolean') THEN
                    'logico'                  
                 WHEN (formato_tipo_atributo = 'bytea') THEN
                    'objeto_binario_grande'
                 ELSE
                    formato_tipo_atributo
            END AS "Tipo"
          , CASE WHEN (nao_nulo IS TRUE) THEN
               'X'
            END AS "Não Nulo"        
          , comentario_coluna AS "Descrição"
          , dominio AS "Dominio"
          , pri AS "PRI"
          , est AS "EST"        
          , can AS "CAN"
          , CASE WHEN (padrao = 'falsevalue()') THEN
                    'falso'
                 WHEN (padrao = 'false') THEN
                    'falso'
                 WHEN (padrao = 'truevalue()') THEN
                    'verdadeiro'
                 WHEN (padrao = 'true') THEN
                    'verdadeiro'
                 WHEN (padrao = 'now()') THEN
                    'agora()'
                 WHEN (padrao = '(\'now\'::text)::date') THEN
                    'agora() :: data'
                 WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN
                    'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8)
                 WHEN (POSITION('::' IN padrao) > 0) THEN
                    SUBSTRING(padrao,'(.*?)\\::')                                
                 ELSE
                    padrao                  
             END AS "Padrão"
          , "check" AS "Check"
       FROM vw_list_dict_data
);
COMMENT ON VIEW vw_list_dict_data_docx IS '
Autor: O Peregrino
View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao
SELECT * FROM vw_list_dict_data_docx;
';

--view que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, usa funcao de usuario sp_tradutor, sp_rm_special_chars, sp_comment_domain, verificar dependencias
DROP VIEW IF EXISTS vw_list_dict_data_doc;
CREATE OR REPLACE VIEW vw_list_dict_data_doc AS
(
     SELECT
            esquema
          , tabela
          , ordem_atributo_tabela         
          , "Esquema.Tabela"
          , "Atributo"
          , "Tipo"
          , "Não Nulo"
          , "Descrição"
          , CASE WHEN "Domínio" IS NOT NULL THEN
               "Domínio" || CHR(10) || 'DOMÍNIO DISCRETO: ' || COALESCE(sp_comment_domain("Descrição"),'')
            ELSE
               COALESCE(sp_comment_domain("Descrição"),'')
            END AS "Domínio"
          , "PRI"
          , "EST"
          , "CAN"
          , "Padrão"
          , "Check"
       FROM
          (


             SELECT
                    esquema
                  , tabela
                  , esquema_tabela AS "Esquema.Tabela"
                  , ordem_atributo_tabela
                  , nome_atributo AS "Atributo"
                  , CASE WHEN (formato_tipo_atributo = 'integer') THEN
                            'inteiro'
                         WHEN (formato_tipo_atributo = 'smallint') THEN
                            'inteiro_pesqueno'
                         WHEN (formato_tipo_atributo = 'bigint') THEN   
                            'inteiro_grande'
                         WHEN (formato_tipo_atributo = 'numeric') THEN                   
                            'numerico'
                         WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN
                            'numerico' || SUBSTRING(formato_tipo_atributo FROM 8)              
                         WHEN (formato_tipo_atributo = 'real') THEN
                            'real'
                         WHEN (formato_tipo_atributo = 'double precision') THEN
                            'dapla_precisao'                                      
                         WHEN (formato_tipo_atributo = 'date') THEN
                            'data'                  
                         WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN
                            'data_hora'
                         WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN
                            'data_hora'  
                         WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN
                            'data_hora'
                         WHEN (formato_tipo_atributo = 'time without time zone') THEN
                            'hora'
                         WHEN (formato_tipo_atributo = 'time with time zone') THEN
                            'hora'                  
                         WHEN (formato_tipo_atributo = 'text') THEN
                            'texto'
                         WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN
                            'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18)
                         WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN
                            'caracter' || SUBSTRING(formato_tipo_atributo FROM 10)                  
                         WHEN (formato_tipo_atributo = '"char"') THEN
                            'caracter'
                         WHEN (formato_tipo_atributo = 'bpchar') THEN
                            'caracter'
                         WHEN (formato_tipo_atributo = 'boolean') THEN
                            'logico'                  
                         WHEN (formato_tipo_atributo = 'bytea') THEN
                            'objeto_binario_grande'
                         ELSE
                            formato_tipo_atributo
                    END AS "Tipo"
                  , CASE WHEN (nao_nulo IS TRUE) THEN
                       'X'
                    END AS "Não Nulo"        
                  , sp_rm_special_chars(comentario_coluna) AS "Descrição"
                  , dominio AS "Domínio"
                  , pri AS "PRI"
                  , est AS "EST"        
                  , can AS "CAN"
                  , CASE WHEN (padrao = 'falsevalue()') THEN
                            'falso'
                         WHEN (padrao = 'false') THEN
                            'falso'
                         WHEN (padrao = 'truevalue()') THEN
                            'verdadeiro'
                         WHEN (padrao = 'true') THEN
                            'verdadeiro'
                         WHEN (padrao = 'now()') THEN
                            'agora()'
                         WHEN (padrao = '(\'now\'::text)::date') THEN
                            'agora() :: data'
                         WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN
                            'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8)
                         WHEN (POSITION('::' IN padrao) > 0) THEN
                            SUBSTRING(padrao,'(.*?)\\::')                                
                         ELSE
                            padrao                  
                     END AS "Padrão"
                  ,  sp_tradutor("check") AS "Check"
               FROM vw_list_dict_data
          ) AS z
);
COMMENT ON VIEW vw_list_dict_data_doc IS '
Autor: O Peregrino
View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, esta função sp_tradutor, sp_rm_special_chars, sp_comment_domain, ver dependencias.
SELECT * FROM vw_list_dict_data_doc;
';

--view que lista a ordem de criação das tabelas.
DROP VIEW IF EXISTS  vw_order_tables_created;
CREATE OR REPLACE VIEW  vw_order_tables_created AS
(
     SELECT tab_pk.relname AS pk,
            tab_fk.relname AS fk
       FROM pg_class AS tab_pk
 INNER JOIN pg_constraint
         ON tab_pk.oid = pg_constraint.confrelid
 INNER JOIN pg_class AS tab_fk
         ON pg_constraint.conrelid = tab_fk.oid
      WHERE tab_pk.relkind = 'r'
   ORDER BY tab_pk.oid ASC
);
COMMENT ON VIEW vw_order_tables_created IS ' 
Autor: O Peregrino
View que lista a ordem de criação das tabelas.
OBS: Em testes.
SELECT * FROM vw_order_table_created;
'
; 
--
-- Nome Artefato/Programa..: monitor_ddl.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 01/03/2010
-- Data Atual..............: 08/09/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: views para monitoramento das atividades do postgres 8.3 /8.4
-- OBS.....................:

-- Script de monitoramento de banco de dados Postgres 8.x DLL
-- Autor: O Peregrino

-- functions
--
-- Nome Artefato/Programa..: sp_tradutor.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 30/08/2010
-- Data Atual..............: 30/08/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para fazer tradução de english para portugues de algumas palavras usadas no postgres
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS sp_tradutor (TEXT) CASCADE;
CREATE OR REPLACE FUNCTION sp_tradutor (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo TEXT;
BEGIN
  stextonovo := REPLACE(stexto,'OR','OU');     
  stextonovo := REPLACE(stextonovo, 'AND','E');  
  stextonovo := REPLACE(stextonovo, 'NOT','NÃO');
  stextonovo := REPLACE(stextonovo, 'IS',''); 
  stextonovo := REPLACE(stextonovo, 'NULL','NULO');
  stextonovo := REPLACE(stextonovo, 'integer','inteiro');
  stextonovo := REPLACE(stextonovo, 'smallint','inteiro_pequeno');
  stextonovo := REPLACE(stextonovo, 'bigint','inteiro_grande');
  stextonovo := REPLACE(stextonovo, 'numeric','numerico');
  stextonovo := REPLACE(stextonovo, 'double precision','dupla_precisao');
  stextonovo := REPLACE(stextonovo, 'date','data');
  stextonovo := REPLACE(stextonovo, 'timestamp without time zone','data_hora');
  stextonovo := REPLACE(stextonovo, 'timestamp with time zone','data_hora');
  stextonovo := REPLACE(stextonovo, 'timestamp(0) without time zone','data_hora');
  stextonovo := REPLACE(stextonovo, 'time without time zone','hora');
  stextonovo := REPLACE(stextonovo, 'text','texto');
  stextonovo := REPLACE(stextonovo, 'character varying','v_caracter');
  stextonovo := REPLACE(stextonovo, 'character','caracter');
  stextonovo := REPLACE(stextonovo, '"char"','caracter');
  stextonovo := REPLACE(stextonovo, 'bpchar','caracter');
  stextonovo := REPLACE(stextonovo, 'boolean','logico');
  stextonovo := REPLACE(stextonovo, 'bytea','objeto_binario_grande');
  stextonovo := REPLACE(stextonovo, 'year','ano');  
  stextonovo := REPLACE(stextonovo, 'month','mes');  
  stextonovo := REPLACE(stextonovo, 'day','dia');  
  stextonovo := REPLACE(stextonovo, 'now()','agora()');  

  RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER;

--SELECT sp_tradutor('(year((data_cadastro)::timestamp with time zone) >= year(now()))') AS test1;

--
-- Nome Artefato/Programa..: sp_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 09/10/2008
-- Data Atual..............: 17/08/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS sp_rm_special_chars (TEXT);
CREATE OR REPLACE FUNCTION sp_rm_special_chars (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo TEXT;
BEGIN
  -- Tabela ASCII
  -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32
                                                  --  hex d abr ctl descricao
                                                  --  00  0 NUL ^@    Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata
  stextonovo := TRANSLATE(stexto, ' ','');       --  01  1 SOH ^A    Start of Header - Início do cabeçalho
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  02  2 STX ^B    Start of Text - Início do texto
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  03  3 ETX ^C    End of Text - Fim do texto
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  04  4 EOT ^D    End of Tape - Fim de fita
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  05  5 ENQ ^E    Enquire - Interroga identidade do terminal
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  06  6 ACK ^F    Acknowledge - Reconhecimento
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  07  7 BEL ^G    Bell - Campainha
  stextonovo := TRANSLATE(stextonovo, '\8','');   --  08  8 BS     ^H  Back-space - Espaço atrás
  stextonovo := TRANSLATE(stextonovo, '\9','');   --  09  9 HT     ^I  Horizontal Tabulation - Tabulação horizontal
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  0A 10 LF  ^J    Line-Feed - Alimenta linha
  stextonovo := TRANSLATE(stextonovo, '\11','');  --  0B 11 VT  ^K     Vertical Tabulation - Tabulação vertical
  stextonovo := TRANSLATE(stextonovo, '\12','');  --  0C 12 FF  ^L    Form-Feed - Alimenta formulário
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  0D 13 CR  ^M    Carriage-Return - Retorno do carro (enter)
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  0E 14 SO  ^N    Shift-Out - Saída do shift (passa a usar caracteres de baixo da tecla - minúsculas, etc.)
  stextonovo := TRANSLATE(stextonovo, '\15','');  --  0F 15 SI  ^O    Shift-In-Ent. no shift (passa a usar carac. de cima da tecla: maiúsculas, carac. especiais, etc.)
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  10 16 DLE ^P    Data-Link Escape
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  11 17 DC1 ^Q    Device-Control 1
  stextonovo := TRANSLATE(stextonovo, ' 8','');  --  12 18 DC2 ^R    Device-Control 2
  stextonovo := TRANSLATE(stextonovo, ' 9','');  --  13 19 DC3 ^S    Device-Control 3
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  14 20 DC4 ^T    Device-Control 4
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  15 21 NAK ^U    Neg-Acknowledge - Não-reconhecimento
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  16 22 SYN ^V    Synchronous Idle
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  17 23 vETB^W     End-of-Transmission Block
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  18 24 CAN ^X    Cancel
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  19 25 EM     ^Y  End-Of-Medium
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1A 26 SUB ^Z    Substitute
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1B 27 ESC ^[    Escape
  stextonovo := TRANSLATE(stextonovo, ' 8','');  --  1C 28 FS     ^\  File Separator
  stextonovo := TRANSLATE(stextonovo, ' 9','');  --  1D 29 GS  ^]    Group Separator
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1E 30 RS  ^^     Record Separator
  stextonovo := TRANSLATE(stextonovo, ' ','');   --  1F 31 US  ^_    Unit Separator
  stextonovo := TRANSLATE(stextonovo, '\127',''); --  7F127 DEL ^?     Delete

  RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

--SELECT 'SEM STRESS...   W TECLA ENTER TECLA ESC TESTE OK ' AS test1

--
-- Nome Artefato/Programa..: sp_comment_domain.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)
-- Data Inicio ............: 31/08/2010
-- Data Atual..............: 31/08/2010
-- Versao..................: 0.01
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para fazer tratamento dos comentarios de dominio
-- OBS.....................:
--
--habilitando linguagem plpgsql
--CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
-- languages
--SELECT * FROM pg_language;
-- Apaga function se existir
DROP FUNCTION IF EXISTS sp_comment_domain (TEXT);
CREATE OR REPLACE FUNCTION sp_comment_domain (stexto TEXT) RETURNS TEXT AS
$$
DECLARE
  stextonovo             TEXT;
  stexto_transitorio     TEXT;
  limite                 INTEGER;
BEGIN

     SELECT INTO limite array_upper(string_to_array(stexto, '§§'),1);

     IF limite > 1 THEN
   
          stextonovo := '';
        
          FOR i IN 2 .. limite LOOP
               SELECT INTO stexto_transitorio a.ar[i] FROM (SELECT string_to_array(stexto, '§§') AS ar ) AS a ;
               stextonovo  := stextonovo || stexto_transitorio || CHR(9);  --9 tabulacao horizontal ou 10 proxima linha ou 11 tabulacao vertical
          END LOOP;
        
     END IF;

     RETURN stextonovo;
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED ON NULL INPUT SECURITY INVOKER;
--STABLE CALLED RETURNS NULL ON NULL INPUT SECURITY INVOKER;

/*

SELECT sp_comment_domain ('Status do bem referente a sua situação negocial. Representado pela classe de constantes: br.ufrn.sipac.patrimonio.tombamento.dominio.TipoStatus, podendo ter tais características:
§§ EFETIVADO = 1 (Quando o bem acaba de ser tombado, sua situação fica normal para realizar operações.),
§§ DEVOLVIDO = 2 (Para bens de terceiros, quando devolvido a entidade terceira que o cedeu),
§§PENDENTE = 3. (Bem terceiro não finalizado e que pertence à entidade que o adquiriu.),
§§ ALIENADO = 4 (Bem que sofreu processo de alienação),
§§ ACAUTELADO = 5 (Bem que sofreu processo de acautelamento),
§§ EM_PROCESSO_ALIENACAO = 6 (Bem que está em processo de alienação),
§§ EM_MOVIMENTACAO = 7 (Bem que está sob movimentação temporária).') AS test1

*/


--views deprecated
/*
DROP VIEW IF EXISTS view_close_db;
DROP VIEW IF EXISTS view_con;
DROP VIEW IF EXISTS view_disk_mem;
DROP VIEW IF EXISTS view_monitor;
DROP VIEW IF EXISTS view_monitor2;
DROP VIEW IF EXISTS view_size_db;
DROP VIEW IF EXISTS view_status_locks;
DROP VIEW IF EXISTS view_status_locks_tables;
DROP VIEW IF EXISTS view_time_server;
--view renomeada para vw_db_size;
DROP VIEW IF EXISTS vw_size_db;
--views criadas com dono errado
DROP VIEW IF EXISTS vw_close_db;
DROP VIEW IF EXISTS vw_con;
DROP VIEW IF EXISTS vw_disk_mem;
DROP VIEW IF EXISTS vw_find;
DROP VIEW IF EXISTS vw_monitor;
DROP VIEW IF EXISTS vw_size_db;
DROP VIEW IF EXISTS vw_status_locks;
DROP VIEW IF EXISTS vw_status_locks_tables;
DROP VIEW IF EXISTS vw_time_server;
--
*/

-- view lista status das tabelas relativo a locks
DROP VIEW IF EXISTS vw_status_locks;
CREATE OR REPLACE VIEW vw_status_locks AS
(
-- locks em andamento

      SELECT d.client_addr AS "maquina",
             c.datname AS "banco",
             e.schemaname AS "esquema",
             b.relname AS "tabela",
             a.mode,
             CASE
               WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela'
               WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela'
           WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO'
               WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela'
               WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela'
             END AS "modo"
        FROM pg_locks a
  INNER JOIN pg_class b
          ON b.oid = a.relation
         AND b.relkind = 'r'
  INNER JOIN pg_tables e
          ON b.relname = e.tablename
  INNER JOIN pg_database c
          ON c.oid = a.database
  INNER JOIN pg_stat_activity d
          ON d.procpid = a.pid
       WHERE relname NOT LIKE 'pg_%'
            --AND a.mode  = 'ExclusiveLock' -- Travamento da tabela
         --AND a.mode  = 'RowExclusiveLock' -- Travamento do registro da tabela
    ORDER BY modo DESC,
             --a.mode ASC,
             d.client_addr ASC,
             b.relname ASC
 )
;
COMMENT ON VIEW vw_status_locks IS '
Autor: O Peregrino
View que verifica situacao de locks em andamento no banco'
;


-- view que mostra locks em andamento de registros e tabelas
DROP VIEW IF EXISTS vw_status_locks_tables;
CREATE OR REPLACE VIEW vw_status_locks_tables AS
(
      SELECT d.client_addr AS "maquina",
             c.datname AS "banco",
             e.schemaname AS "esquema",
             b.relname AS "tabela",
             a.mode,
             CASE
               WHEN a.mode = 'AccessShareLock' THEN 'Acesso a tabela'
               WHEN a.mode = 'ExclusiveLock' THEN 'Travamento da tabela'
           WHEN a.mode = 'AccessExclusiveLock' THEN 'Travamento da tabela - ACESSO EXCLUSIVO'
               WHEN a.mode = 'RowShareLock' THEN 'Acesso ao registro da tabela'
               WHEN a.mode = 'RowExclusiveLock' THEN 'Travamento do registro da tabela'
             END AS "modo"
        FROM pg_locks a
  INNER JOIN pg_class b
          ON b.oid = a.relation
         AND b.relkind = 'r'
  INNER JOIN pg_tables e
          ON b.relname = e.tablename
  INNER JOIN pg_database c
          ON c.oid = a.database
  INNER JOIN pg_stat_activity d
          ON d.procpid = a.pid
       WHERE relname NOT LIKE 'pg_%'
        AND a.mode  = 'ExclusiveLock' -- Travamento da tabela
        AND a.mode  = 'RowExclusiveLock' -- Travamento do registro da tabela
    ORDER BY modo DESC,
             --a.mode ASC,
             d.client_addr ASC,
             b.relname ASC
 )
;
COMMENT ON VIEW vw_status_locks_tables IS '
Autor: O Peregrino
View que verifica situacao apenas de locks de registros e tabelas no banco'
;


--view para mostrar o uso de memoria disco do postgres
DROP VIEW IF EXISTS vw_disk_mem;
CREATE OR REPLACE VIEW vw_disk_mem AS
(
SELECT (SUM(pg_stat_database.blks_hit) / SUM(pg_stat_database.blks_read + pg_stat_database.blks_hit) * 100::NUMERIC)::INTEGER AS "% de Utilização de Mem"
  FROM pg_stat_database
)
;
COMMENT ON VIEW vw_disk_mem IS '
Autores: O Peregrino (apenas a view) Kenia Milene |http://keniamilene.wordpress.com| (query)
View que calcula através das estatisticas o percentual de utilização disco / memória.
Valores acima de 70% significa que o banco esta realizado mais tarefas em memória do que i/o em disco';


-- view para listar conexoes ativas e transações efetuadas (commit-rollback)
DROP VIEW IF EXISTS vw_con;     
CREATE OR REPLACE VIEW vw_con AS
(
   
     SELECT DISTINCT datname,
                     pg_stat_get_db_xact_commit(datid) AS "commits",
                     pg_stat_get_db_xact_rollback(datid) AS "rollbacks",
                     pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid) AS "transacoes_efetuadas",
                     CASE WHEN pg_stat_get_db_xact_commit(datid) = 0 THEN  -- evita divisao por zero
                       0
                     ELSE
                       (pg_stat_get_db_xact_commit(datid) + pg_stat_get_db_xact_rollback(datid))/(pg_stat_get_db_xact_commit(datid)/100)
                     END AS "%",
                     pg_stat_get_db_numbackends(datid) AS conexoes_ativas
                FROM pg_stat_activity
            ORDER BY 5 DESC
)
;
COMMENT ON VIEW vw_con IS '
Autor: O Peregrino
View que mostra conexoes ativas e transações efetuadas (commit-rollback)'
;


-- view para monitoramento de atividade do servidor postgres por usuario
DROP VIEW IF EXISTS vw_monitor;     
CREATE OR REPLACE VIEW  vw_monitor AS
(
         SELECT procpid,
            datname AS banco,
            usename AS usuario_banco,
            client_addr AS ip,
            CASE WHEN waiting IS TRUE THEN
              'Aguardando'
            ELSE
              'Executando'
            END AS status,
            AGE(now(),query_start) AS tempo_execucao,
          
            current_query
           FROM pg_stat_activity
          WHERE 1=1
              AND current_query <> '' -- DESOCUPADO
       ORDER BY tempo_execucao DESC
                --current_query DESC
               --client_addr ASC,
               --procpid ASC
)
;
COMMENT ON VIEW vw_monitor IS '
Autor: O Peregrino
View que verifica o que está executando no banco no instante atual incluido transacoes, listado  por usuario e ip e por tempo
Adequado para super-usuario
--SELECT *
   FROM vw_monitor;
OBS:
Para cancelar processo executar os comandos abaixos 
-- cancelar query >= 8.0
--SELECT pg_cancel_backend(21471);
-- cancelar processo >= 8.4
--SELECT pg_terminate_backend(21471);
'
;


/*
-- DESATIVADA POIS NECESSITA DE TABELAS CRIADA PELO DBA E REDE COM IP FIXOS
-- (DBA) - monitoramento de atividade do servidor postgres por usuario
DROP VIEW IF EXISTS vw_monitor2;
CREATE OR REPLACE VIEW vw_monitor2 AS
(
     SELECT a.procpid,
            a.datname AS banco,
            a.usename AS usuario_banco,
            a.client_addr AS ip,
            b.nome,
            b.talk,
            b.sistema,
            CASE WHEN a.waiting IS TRUE THEN
              'Aguardando'
            ELSE
              'Executando'
            END AS status,
            AGE(now(),a.query_start) AS tempo_execucao,
        a.current_query
       FROM pg_stat_activity a
  LEFT JOIN system.users b
         ON a.client_addr = TRIM(b.ip)::inet
      WHERE 1=1
      --AND a.usename = 'sipac'
      --AND a.datname LIKE 'administrativo%'
        AND a.current_query <> '' -- DESOCUPADO
      --AND a.usename = 'postgres'
      --AND a.client_addr = '10.3.128.136' -- Emerson
      --AND a.client_addr = '10.3.128.70' -- Itamir
   ORDER BY --a.current_query DESC,
            tempo_execucao DESC,
            b.nome ASC
            --a.client_addr ASC,
            --a.procpid ASC
)          
;

COMMENT ON VIEW vw_monitor2 IS '
Verifica o que está executando no banco no instante atual, incluindo transacoes, listado por usuario e ip e por tempo usando tabela system.users
Adequado para super-usuario
--SELECT *
   FROM vw_monitor;
OBS:
Para cancelar processo executar os comandos abaixos 
-- cancelar query >= 8.0
--SELECT pg_cancel_backend(21471);
-- cancelar processo >= 8.4
--SELECT pg_terminate_backend(21471);
'
;


-- estrutura de tabela a ser criada pelo DBA para funcionamento da view vw_monitor2

DROP SCHEMA IF EXISTS "system";
CREATE SCHEMA "system";
DROP TABLE IF EXISTS "system".users;
CREATE TABLE "system".users
(
  id           SERIAL                   NOT NULL,
  nome         CHARACTER VARYING(50)    NOT NULL,
  talk         CHARACTER VARYING(200),
  email        CHARACTER VARYING(200),
  ramal        CHARACTER VARYING(20),
  celular      CHARACTER VARYING(350),
  residencial  CHARACTER VARYING(350),
  computador   CHARACTER VARYING(200),
  ip           CHARACTER VARYING(200)   NOT NULL,
  sistema      CHARACTER VARYING(20),
  obs          TEXT,
  CONSTRAINT users_pkey PRIMARY KEY (id)
);


INSERT INTO "system".users (id, nome, talk, email, ramal, celular, residencial, computador, ip, sistema, obs)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?,  ?, ?, ?);

SELECT * FROM "system".users;

*/


-- view que mostra o tempo de execucao do servidor corrente
DROP VIEW IF EXISTS vw_time_server;
CREATE OR REPLACE VIEW vw_time_server AS
(

     -- quando o servidor do postgres foi iniciado, tempo?
     SELECT TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in,
            AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age
)
;
COMMENT ON VIEW vw_time_server IS '
Autor: O Peregrino
View que mostra quanto o servidor do postgres foi iniciado, tempo de execucao'
;


-- view que mostra tamanho dos bancos
DROP VIEW IF EXISTS vw_db_size;
CREATE OR REPLACE VIEW vw_db_size AS
(
     --tamanho dos bancos em MB
     SELECT datname AS banco,
            pg_database_size(datname)/1000000 || ' MB' AS tamanho_bd
       FROM pg_database
   ORDER BY tamanho_bd DESC
)
;  
COMMENT ON VIEW vw_db_size IS '
Autor: O Peregrino
View que mostra o tamanho dos bancos em MB'
;


-- view  que executa tentativa de fechamento de conexoes, necessario permissao de super-usuario,
-- em testes
DROP VIEW IF EXISTS vw_close_db;
CREATE OR REPLACE VIEW vw_close_db AS
(
     SELECT procpid
            , datname AS banco
            , client_addr AS ip
            , pg_cancel_backend(procpid) AS sit
       FROM pg_stat_activity
  
)
;
COMMENT ON VIEW vw_close_db IS '
Autor: O Peregrino
Derruba todos os usuario conectados.
Necessário permissao de super-usuario
OBS: Em testes'
;


-- view para descobrir esquema de uma tabela ou quais as tabelas existentes em um esquema por quantidade de registros
DROP VIEW IF EXISTS vw_find CASCADE;
CREATE OR REPLACE VIEW vw_find AS
(
     SELECT
            n.nspname AS esquema
            , 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
            , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_sem_indices
            , pg_size_pretty(pg_total_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_com_indices             
       FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n
         ON n.oid = c.relnamespace
      WHERE c.relkind = 'r'
        AND n.nspname NOT IN (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'                                
                             )
        AND n.nspname NOT ILIKE 'pg_temp_%'
        AND n.nspname NOT ILIKE 'pg_ts_%'
        AND c.relname NOT ILIKE 'pg_ts_%'
   ORDER BY 1 ASC

)
; 
COMMENT ON VIEW vw_find IS '
Autor: O Peregrino
View que procura por tabelas, esquemas, comentários, donos no banco
--exemplos:

--busca simples (tudo)
     SELECT *
       FROM vw_find
     ;

--busca por esquema
     SELECT *
       FROM vw_find
      WHERE esquema ILIKE \\\'esquema\\\'
      ;

--busca por tabelas
     SELECT *
       FROM vw_find
      WHERE tabela ILIKE \\\'%tabela%\\\'
      ;
     
'
;
/*
--exemplos de vw_find:
--busca simples (tudo)
     SELECT *
       FROM vw_find
     ;
--busca por esquema
     SELECT *
       FROM vw_find
      WHERE esquema ILIKE 'esquema'
      ;
--busca por tabelas
     SELECT *
       FROM vw_find
      WHERE tabela ILIKE '%tabela%'
      ;
     
*/


-- view para procuar as sequencias
DROP VIEW IF EXISTS vw_find_seq;
CREATE OR REPLACE VIEW vw_find_seq AS
(
     SELECT n.nspname AS esquema,
            c.relname AS seq,
            pg_catalog.pg_get_userbyid(c.relowner) AS dono,
            pg_catalog.obj_description(c.oid, 'pg_class') AS comentario,
            reltuples::integer AS registros
       FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n
         ON n.oid = c.relnamespace
      WHERE c.relkind = 'S' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table
        AND n.nspname NOT IN (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
   ORDER BY n.nspname,
            c.relname

);
COMMENT ON VIEW vw_find_seq IS '
Autor: O Peregrino
View que procura por esquemas, sequencia, comentários, donos no banco
--exemplos:

--busca simples (tudo)
     SELECT *
       FROM vw_find_seq
     ;

--busca por esquema
     SELECT *
       FROM vw_find_seq
      WHERE esquema ILIKE \\\'esquema\\\'
      ;

--busca por tabelas
     SELECT *
       FROM vw_find_seq
      WHERE seq ILIKE \\\'%seq%\\\'
      ;
     
'
;

-- view para listar todas a views de usuario
DROP VIEW IF EXISTS vw_list_views;
CREATE OR REPLACE VIEW vw_list_views AS
(
     SELECT *
       FROM  pg_views
       WHERE schemaname NOT IN (
                                'pg_catalog',
                                'information_schema',
                                'pg_toast'
                               )
);
COMMENT ON VIEW vw_list_views IS '
Autor: O Peregrino
View que lista todas as views dos usuarios no banco
--SELECT * FROM vw_list_views;'
;


-- view que lista todas as colunas de todas as tabelas
DROP VIEW IF EXISTS vw_find_attrib CASCADE;
CREATE OR REPLACE VIEW vw_find_attrib AS
(
     SELECT
             b.esquema
           , b.tabela
           , b.oid_tabela
           , b.registros
           , a.attname AS "nome_atributo"
           , pg_catalog.format_type(a.atttypid, a.atttypmod) AS "formato_tipo_atributo"
           , a.attnotnull AS "nao_nulo"
           , a.attnum AS "ordem_atributo_tabela"
           , pg_catalog.col_description(b.oid_tabela, a.attnum) AS "comentario_coluna"
           , (
               SELECT SUBSTRING(pg_catalog.pg_get_expr(d.adbin, d.adrelid) FOR 128)
                 FROM pg_catalog.pg_attrdef d
                WHERE d.adrelid = a.attrelid
                  AND d.adnum = a.attnum
                  AND a.atthasdef
             ) AS padrao
       FROM (
             SELECT c.oid AS "oid_tabela",
                    c.relname AS "tabela",    
                    n.nspname AS "esquema",
                    reltuples::integer AS "registros"
               FROM pg_catalog.pg_class c
          LEFT JOIN pg_catalog.pg_namespace n
                 ON n.oid = c.relnamespace
              WHERE c.relkind = 'r' -- r = ordinary table, i = index, S = sequence, v = view, c = composite type, t = TOAST table
                AND n.nspname NOT IN
                             (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
           ORDER BY reltuples::integer DESC               
           ) AS b
 INNER JOIN pg_catalog.pg_attribute a
         ON a.attrelid = b.oid_tabela        
      WHERE 1=1
        AND a.attnum > 0 -- despreza 'oid','cmax','xmax','cmin','xmin','ctid','tableoid'
        AND NOT a.attisdropped  -- colunas excluidas
   ORDER BY b.registros DESC,
            b.esquema ASC, 
            b.oid_tabela ASC,
            ordem_atributo_tabela ASC
)
;
COMMENT ON VIEW vw_find_attrib IS '
Autor: O Peregrino
View que procura por nome_atributo, formato_tipo_atributo, etc.
--SELECT * FROM vw_find_attrib WHERE comentario_coluna IS NULL;'
;  


-- view para listar os bancos liberados ou nao para acesso
DROP VIEW IF EXISTS vw_db_list_access;
CREATE OR REPLACE VIEW vw_db_list_access AS
(
           SELECT a.datname AS banco,
                  b.usename AS dba,
                  CASE WHEN a.datallowconn = TRUE THEN
                    'LIBERADO ACESSO'
                  ELSE
                    'BLOQUEADO ACESSO'
                  END AS situacao
             FROM pg_database a
       INNER JOIN pg_user b
               ON b.usesysid = a.datdba
);

COMMENT ON VIEW vw_db_list_access IS '
Autor: O Peregrino
View que lista bancos com acesso liberados ou bloqueados.
--OBS: Apenas super-usuario pode executar as clausulas abaixo
--Para bloquear um banco
--ALTER DATABASE foo CONNECTION LIMIT 0; --(so super-usuários podem se conectar)
--Para desbloquear um banco
--ALTER DATABASE foo CONNECTION LIMIT -1; --(so super-usuários podem se conectar)
--SELECT * FROM vw_db_list_access;
'
;  


-- view que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido
DROP VIEW IF EXISTS vw_table_relat;
CREATE OR REPLACE VIEW vw_table_relat AS
(
     SELECT c.oid,
            n.nspname AS esquema,
            c.relname AS tabela,
            conname,
            consrc
       FROM (
             SELECT conname,
                    pg_catalog.pg_get_constraintdef(oid) AS consrc,
                    conrelid as relid
               FROM pg_catalog.pg_constraint
              WHERE contype = 'f'
            ) AS subq
       JOIN pg_catalog.pg_class c ON relid = c.oid
       JOIN pg_namespace AS n ON n.oid = c.relnamespace
);

COMMENT ON VIEW vw_table_relat IS '
Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query)
View que retorna no nome das tabelas que se relacionam com outra cujo o nome é conhecido
--SELECT * FROM vw_table_relat;'
;  



--view que mostra as tabelas que estao com tuplas mortas e precisam de vacuum
DROP VIEW IF EXISTS vw_dead_tuples;
CREATE OR REPLACE VIEW vw_dead_tuples AS
(

     SELECT *
       FROM pg_stat_all_tables
      WHERE n_dead_tup > 0
   ORDER BY n_dead_tup DESC
)
;

COMMENT ON VIEW vw_dead_tuples IS '
Autor: O Peregrino
View que retorna no nome das tabelas que estao com tuplas mortas e precisam de vacuum
--SELECT * FROM vw_dead_tuples;'
;  


-- view que mostrar quantidade de usuario conectados no banco
DROP VIEW IF EXISTS vw_db_users_number;
CREATE OR REPLACE VIEW vw_db_users_number AS
(

    SELECT COUNT(*) AS total
      FROM (
             SELECT
           DISTINCT client_addr
               FROM pg_stat_activity
            ) AS x
);

COMMENT ON VIEW vw_db_users_number IS '
Autor: O Peregrino
View que retorna quantos usuarios conectados ao banco
--SELECT * FROM vw_db_users_number;
'
;  


--view para identificação de índices duplicados
DROP VIEW IF EXISTS vw_idx_duplicate;
CREATE OR REPLACE VIEW vw_idx_duplicate AS
(
        SELECT pg_stat_user_indexes.schemaname AS esquema
             , pg_stat_user_indexes.relname AS tabela
             , pg_attribute.attname AS nome_atributo
             , pg_stat_user_indexes.indexrelname AS nome_indice
             , CASE pg_index.indisprimary WHEN 't' THEN
                 'Sim'
                  ELSE
                 'Nao'
               END
            AS indice_na_chave_primaria
          FROM pg_index
          JOIN pg_stat_user_indexes USING (indexrelid)
          JOIN
               (
                SELECT pg_index.indrelid,
                       pg_index.indkey,
                       count(*)
                  FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid)
              GROUP BY pg_index.indrelid
                       , pg_index.indkey
                HAVING count(*)>1
                ) ind_dup
             ON pg_index.indrelid=ind_dup.indrelid
                AND pg_index.indkey=ind_dup.indkey
           JOIN pg_attribute
             ON pg_attribute.attrelid=ind_dup.indrelid
                AND pg_attribute.attnum=SOME(pg_index.indkey)
       ORDER BY pg_stat_user_indexes.schemaname
              , pg_stat_user_indexes.relname
              , pg_index.indisprimary='t' DESC
);
COMMENT ON VIEW vw_idx_duplicate IS '
Autores: O Peregrino (apenas a view) JotaComm |http://jotacomm.wordpress.com| (query)
View que retorna identificação de índices duplicados no banco
--SELECT * FROM vw_idx_duplicate;'
;  


--view que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique)
DROP VIEW IF EXISTS vw_list_unique CASCADE;
CREATE OR REPLACE VIEW vw_list_unique AS
(
    SELECT
   DISTINCT
            x.nome_constraint
          , x.esquema
          , c2.relname AS tabela
          , x.coluna AS nome_atributo         
       FROM
            (
               SELECT           
                      n.nspname AS esquema
                    , c.relname AS nome_constraint
                    , at.attname AS coluna
                    , cn.conrelid AS oid
                 FROM pg_catalog.pg_constraint cn
           INNER JOIN pg_catalog.pg_index i
                   ON (cn.conrelid = i.indrelid)
           INNER JOIN pg_catalog.pg_attribute at
                   ON at.attrelid = i.indexrelid
           INNER JOIN pg_catalog.pg_class c
                   ON c.oid = at.attrelid
            LEFT JOIN pg_catalog.pg_namespace n
                   ON n.oid = c.relnamespace              
                WHERE 1=1
                  AND i.indisunique IS TRUE
                  AND i.indisprimary IS FALSE
                  AND cn.contype = 'u' --UNIQUE
             ) AS x
  INNER JOIN pg_catalog.pg_class c2
          ON c2.oid = x.oid
       WHERE 1=1
)
;      
COMMENT ON VIEW vw_list_unique IS '
Autor: O Peregrino
View que retorna nome_constraint, esquema, tabela, coluna em que a constraint informada esta envolvida (unique)
Exemplo: CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor
--SELECT * FROM vw_list_unique;
--Exemplo:
--CONSTRAINT servidor_siape_key UNIQUE (siape, digito_siape); -- tabela servidor'
;  


--view que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check)
-- em teste
DROP VIEW IF EXISTS vw_list_check CASCADE;
CREATE OR REPLACE VIEW vw_list_check AS
(
     SELECT
   DISTINCT
            x.nome_constraint
          , x.esquema
          , c2.relname AS tabela
          , x.coluna AS nome_atributo
          , x.conteudo         
       FROM
            (
               SELECT           
                      n.nspname AS esquema
                    , c.relname AS nome_constraint
                    , at.attname AS coluna
                    , cn.conrelid AS oid
                    , cn.consrc AS conteudo
                 FROM pg_catalog.pg_constraint cn
           INNER JOIN pg_catalog.pg_index i
                   ON (cn.conrelid = i.indrelid)
           INNER JOIN pg_catalog.pg_attribute at
                   ON at.attrelid = i.indexrelid
           INNER JOIN pg_catalog.pg_class c
                   ON c.oid = at.attrelid
            LEFT JOIN pg_catalog.pg_namespace n
                   ON n.oid = c.relnamespace              
                WHERE 1=1
                  AND cn.contype = 'c' --CHECK
             ) AS x
  INNER JOIN pg_catalog.pg_class c2
          ON c2.oid = x.oid
       WHERE 1=1 -- comentar essa linha se quiser listar todos
       
);
COMMENT ON VIEW vw_list_check IS '
Autor: O Peregrino
View que retorna nome_constrainte, esquema, tabela, coluna em que a constraint informada esta envolvida (check)
OBS: Em testes
--SELECT * FROM vw_list_check;  '
;  


-- view que lista todas as chaves primarias (pk) de todas as tabelas
DROP VIEW IF EXISTS vw_list_pk CASCADE;
CREATE OR REPLACE VIEW vw_list_pk AS
(
     SELECT  d.esquema
          ,  b.relname AS tabela
          ,  c.attname AS nome_atributo
          ,  pg_catalog.format_type(c.atttypid, c.atttypmod) AS formato_tipo_atributo
          ,  pg_catalog.col_description(d.oid_tabela, c.attnum) AS comentario_coluna
          ,  c.attnum AS ordem_atributo_tabela
          ,  d.registros
       FROM (
             SELECT c.oid AS "oid_tabela",
                    c.relname AS "tabela",    
                    n.nspname AS "esquema",
                    reltuples::integer AS "registros"
               FROM pg_catalog.pg_class c
          LEFT JOIN pg_catalog.pg_namespace n
                 ON n.oid = c.relnamespace
              WHERE c.relkind = 'r'            
                AND n.nspname NOT IN
                             (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
           ORDER BY reltuples::integer DESC                          
             ) AS d
  INNER JOIN pg_index a
          ON a.indrelid = d.oid_tabela
  INNER JOIN pg_class b
          ON b.oid = a.indrelid
  INNER JOIN pg_attribute c
          ON c.attrelid = b.oid
      WHERE
             b.oid IN (
                       SELECT oid
                         FROM pg_catalog.pg_class
                        WHERE relkind = 'r'
                          AND relname NOT LIKE 'pg_%'
                          AND relname NOT LIKE 'sql_%'
                       )
       AND c.attnum = ANY(a.indkey)
       AND a.indisprimary IS TRUE
       AND a.indisunique IS TRUE
       AND c.attnotnull IS TRUE
       AND c.attrelid = b.oid
       --AND c.attnum > 1
  ORDER BY 1 ASC,
           2 ASC
);
COMMENT ON VIEW vw_list_pk IS '
Autores: O Peregrino / José Flávio DBA |zflavio at gmail.com|
View que retorna uma lista das chaves primarias (pk) do banco
--SELECT * FROM vw_list_pk;
'
;  

--View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados
DROP VIEW IF EXISTS vw_max_id CASCADE;
CREATE OR REPLACE VIEW vw_max_id AS
(
               (
               SELECT 'SELECT '
                   || '\''||x.esquema||'.'||x.tabela||'\''
                   || ' AS esquema_tabela'
                   || ','
                   || ' (SELECT MAX('
                   || x.nome_atributo
                   || ')'
                   || ' FROM '
                   || x.esquema
                   || '.'
                   || '\"'||x.tabela||'\"'
                   || ')::BIGINT AS maior_id UNION '
                   AS maior_ids
                 FROM (
                       SELECT *
                         FROM vw_list_pk
                        WHERE esquema
                       NOT IN ('migracoes'
                               ,'z_deprecated'
                               ,'complexo_hospitalar'
                               ,'espaco_fisico'
                              )
                          AND formato_tipo_atributo NOT IN ('date')
                          AND formato_tipo_atributo NOT ILIKE 'character%'
                          AND tabela NOT ILIKE '@%'
                     ORDER BY 7 DESC
                       ) AS x
               )
         UNION
               (
                 SELECT 'SELECT \'ZZZ-FIM-USADO-PARA-CONTROLE\' AS esquema_tabela,  0::BIGINT AS maior_id ORDER BY 2 DESC;' AS maior_ids
               )
        ORDER BY 1 ASC
);
COMMENT ON VIEW vw_max_id IS '
Autor: O Peregrino
View que retorna uma query com o objetivo de descobrir qual o maior id de todas a tabela de um banco de dados
-- O resultado desta view gera um nova query para obter o resultado
--  desejado.
-- Esta consulta pega o maior id de todas tabelas de um banco.
-- É gerado apenas um coluna como resultado da view com query.
--  para obter o valor maximo de um id em uma tabela, de todas as tabelas de um  banco.
-- Dica para execução:
-- No pgadmin selecione CTRL + A, CTRL + C no resultado da query,
--  Abrir uma nova query, CTRL + V,  CTRL + HOME,
--  CTRL + F, substituir " por nada, recomendado, em vez de " ser |, se estiver configurado
--  Clicar em (Substituir TODOS) e depois em (Fechar)
--  Executar query (f5)
-- Será retornada 2 colunas: esquema_tabela e o maior_id
-- Essa view vw_max_id usa a view vw_list_pk
--SELECT * FROM vw_max_id;
'
;  




-- view para checar compatibilidade de tipo dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos compativeis
DROP VIEW IF EXISTS vw_list_type_not_compatible_relat;
CREATE OR REPLACE VIEW vw_list_type_not_compatible_relat AS
(
SELECT tb_virtual.oid_fk
     , tb_virtual.esquema_fk
     , tb_virtual.tabela_fk
     , tb_virtual.coluna_fk
     , tb_virtual.oid_referenciada
     , tb_virtual.esquema_tabela_referenciada
     , tb_virtual.coluna_referenciada
     , tb_virtual.nome_constraint
     , tb_virtual.sql_constraint
     , pg_catalog.format_type(at.atttypid, at.atttypmod) AS formato_tipo_coluna_referenciada
     , tb_virtual.formato_tipo_coluna_fk
  FROM
      (

          SELECT c.oid AS oid_fk
               , n.nspname AS esquema_fk
               , c.relname AS tabela_fk
               , SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME AS coluna_fk
               , subq.confrelid AS oid_referenciada
               , SUBSTRING(subq.consrc,'REFERENCES (.*)\\(')::NAME AS esquema_tabela_referenciada
               , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME AS coluna_referenciada
               , pg_catalog.format_type(a.atttypid, a.atttypmod) AS formato_tipo_coluna_fk
               , subq.conname AS nome_constraint
               , subq.consrc AS sql_constraint
            FROM (
                     SELECT c.conname
                          , pg_catalog.pg_get_constraintdef(oid) AS consrc
                          , c.conrelid AS relid
                          , c.confrelid
                       FROM pg_catalog.pg_constraint c
                      WHERE c.contype = 'f'
                 ) AS subq
             JOIN pg_catalog.pg_class c
               ON relid = c.oid
             JOIN pg_namespace AS n
               ON n.oid = c.relnamespace
             JOIN pg_catalog.pg_attribute a
               ON a.attrelid = c.oid
             JOIN pg_type AS t
               ON t.oid = a.atttypid
            WHERE 1=1
              AND a.attname IN (
                                  SUBSTRING(subq.consrc,'KEY \\((.*)\\) REFERENCES')::NAME -- coluna_fk
                                , SUBSTRING(SUBSTRING(subq.consrc,'REFERENCES (.*)'),'\\((.*)\\)')::NAME  -- coluna_referenciada
                               )
      ) AS tb_virtual
INNER JOIN pg_catalog.pg_attribute AS at
        ON tb_virtual.oid_referenciada = attrelid
      JOIN pg_type AS tt
       ON tt.oid = at.atttypid
     WHERE attname = tb_virtual.coluna_fk
       AND  tb_virtual.formato_tipo_coluna_fk <> pg_catalog.format_type(at.atttypid, at.atttypmod) -- formato_tipo_coluna_referenciada
  ORDER BY 1 ASC
);

COMMENT ON VIEW vw_list_type_not_compatible_relat IS '
Autor: O Peregrino
View que retorna uma lista de tipos dos campos sendo chaves primaria e estrageiras, relacionamentos com tipos nao compativeis
--SELECT * FROM vw_list_type_not_compatible_relat;  '
;  


-- View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos  correntes no servidor
DROP VIEW IF EXISTS vw_info_utils;
CREATE OR REPLACE VIEW vw_info_utils AS
(
     SELECT TO_CHAR(NOW(),'DD/MM/YYYY HH:MI:SS') AS agora
          , SESSION_USER AS usuario_corrente
          , CURRENT_SCHEMA() AS esquema_corrente
          , CURRENT_DATABASE() AS banco_corrente
          , INET_CLIENT_ADDR() AS ip_cliente
          , INET_CLIENT_PORT() AS porta_cliente
          , INET_SERVER_ADDR() AS ip_servidor
          , INET_SERVER_PORT() AS porta_servidor
          , TO_CHAR(PG_POSTMASTER_START_TIME(),'DD/MM/YYYY HH:MI:SS') AS start_in
          , AGE(NOW(),PG_POSTMASTER_START_TIME()) AS time_run_age
          , VERSION() AS versao_postgres         
          , PG_BACKEND_PID() AS pid_corrente
          , (SELECT COUNT(*) FROM pg_stat_get_backend_idset()) AS qt_pid_processos_correntes_servidor
)
;
COMMENT ON VIEW vw_info_utils IS '
Autor: O Peregrino
View que retorna esquema atual, banco atual, ip do cliente, porta do cliente, ip do servidor, porta do servidor do banco de dados, pid do processo atual, qt de processos  correntes no servidor
--SELECT * FROM vw_info_utils;'
;


-- view que mostra data do ultimo vacuum
DROP VIEW IF EXISTS vw_list_last_vacuum;
CREATE OR REPLACE VIEW vw_list_last_vacuum AS
(
     SELECT schemaname AS esquema
          , relname AS tabela
          , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
          , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
       FROM pg_stat_all_tables
)
;
COMMENT ON VIEW  vw_list_last_vacuum IS '
Autor: O Peregrino
View que retorna uma lista de tabelas com o data do ultimo vacuum
--SELECT * FROM vw_list_last_vacuum;'
;


-- lista funcoes e esquema
DROP VIEW IF EXISTS vw_list_user_functions;
CREATE OR REPLACE VIEW vw_list_user_functions AS
(
     SELECT a.routine_catalog
          , a.routine_schema
          , a.routine_name
          , a.routine_type
          , b.prosrc
       FROM information_schema.routines a
 INNER JOIN pg_proc b
         ON a.routine_name = b.proname
      WHERE 1=1
        AND a.routine_schema NOT IN (
                                         'pg_catalog'
                                         ,'information_schema'
                                         ,'pg_toast'
                                    )
)
;
COMMENT ON VIEW  vw_list_user_functions IS '
Autor: O Peregrino
View que retorna uma lista funcoes por esquema
--SELECT * FROM vw_list_user_functions;'
;


-- view que lista as tablespaces criadas
DROP VIEW IF EXISTS vw_list_tablespaces;
CREATE OR REPLACE VIEW vw_list_tablespaces AS
(
     SELECT spcname
          , pg_catalog.pg_get_userbyid(spcowner) AS spcowner
          , spclocation
       FROM pg_catalog.pg_tablespace
);

COMMENT ON VIEW vw_list_tablespaces IS '
Autor: O Peregrino
View que retorna uma lista das tablespaces criadas
--SELECT * FROM vw_list_tablespaces'
;


-- view que lista os indices ajuda a descobrir os indices de um esquema e ou tabela
DROP VIEW IF EXISTS vw_list_idx;
CREATE OR REPLACE VIEW vw_list_idx AS
(
     SELECT n.nspname AS esquema
          , c.relname AS tabela
          , pg_catalog.pg_get_userbyid(c.relowner) AS dono_tabela
          , c2.relname AS nome_indice
          , CASE WHEN i.indisprimary = TRUE THEN
              'SIM'
            ELSE
              'NAO'
            END AS primary_key
          , CASE WHEN i.indisunique = TRUE THEN
              'SIM'
            ELSE
              'NAO'
            END AS unique
          , i.indisclustered
          , i.indisvalid
          , pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)
        --, c2.reltablespace
          , pg_catalog.obj_description(c2.oid, 'pg_class') AS comentario_indice
          , c.reltuples::integer AS registros_tabela
          , pg_size_pretty(pg_relation_size(n.nspname ||'.'||'"'||c.relname||'"')) AS tamanho_indice
       FROM pg_catalog.pg_class c
  LEFT JOIN pg_catalog.pg_namespace n
         ON n.oid = c.relnamespace,
            pg_catalog.pg_class c2,
            pg_catalog.pg_index i
      WHERE 1=1
        AND c.relkind = 'r'
        AND n.nspname NOT IN (
                               'pg_catalog',
                               'information_schema',
                               'pg_toast'
                             )
        AND c.oid = i.indrelid
        AND i.indexrelid = c2.oid
        AND n.nspname NOT ILIKE 'pg_temp_%'
        AND n.nspname NOT ILIKE 'pg_ts_%'
        AND c.relname NOT ILIKE 'pg_ts_%'
   ORDER BY n.nspname ASC
          , c.relname ASC
          , i.indisprimary DESC
          , i.indisunique DESC
          , c2.relname ASC                             
)
; 
COMMENT ON VIEW vw_list_idx IS '
Autor: O Peregrino
View que lista os indices, isto e, descobrir os indices de um esquema e ou tabela
--SELECT * FROM vw_list_idx;'
;


-- view que lista regras
DROP VIEW IF EXISTS vw_rules;
CREATE OR REPLACE VIEW vw_rules AS
(
     SELECT *
       FROM pg_rules
);
COMMENT ON VIEW vw_rules IS '        
Autor: O Peregrino
View que lista rules
--SELECT * FROM vw_rules;'
;


---view que mostra qual o limite para estouro maximo de transacoes (2 bilhoes)
DROP VIEW IF EXISTS vw_max_burst_transactions;
CREATE OR REPLACE VIEW vw_max_burst_transactions AS
(
     SELECT datname AS banco
          , AGE(datfrozenxid) AS transacoes
          , 2000000000 AS nao_ultrapassar_esse_limite
       FROM pg_database
);
COMMENT ON VIEW vw_max_burst_transactions IS '        
Autor: O Peregrino, baseado na documentação do PostgreSQL 8.0
View que mostra a quantidade de transacoes por banco/servidor realizadas.
OBS: O DBA NAO PODE DEIXAR ULTRAPASSAR 2 BILHOES TRANSACOES!!!
--SELECT * FROM vw_max_burst_transactions;'
;


-- view para tamanho do bloco no disco
DROP VIEW IF EXISTS vw_db_size_block;
CREATE OR REPLACE VIEW vw_db_size_block AS
(
     SELECT *
       FROM pg_settings
      WHERE name='block_size'
);
COMMENT ON VIEW vw_db_size_block IS '
Autor: O Peregrino
View que mostra o tamanho do block no disco
--SELECT * FROM vw_db_size_block;'
;


-- view que mostra o nivel de isolamento de transacoes
DROP VIEW IF EXISTS vw_isolation_level;
CREATE OR REPLACE VIEW vw_isolation_level AS
(
 SELECT name AS isolamento_transacao,
       setting AS configurado
  FROM pg_settings
 WHERE name LIKE 'transaction%'
);
COMMENT ON VIEW vw_isolation_level IS '
Autor: O Peregrino
View que mostra o nivel de isolamento de transacao
--SELECT * FROM vw_isolation_level;'
;


-- view que mostra as dependencias entre tabelas
DROP VIEW IF EXISTS vw_depend_tables;
CREATE OR REPLACE VIEW vw_depend_tables AS
(
     SELECT
            esq_pai.nspname AS esquema_pai   
          , tab_pai.relname AS tabela_pai
          , esq_filho.nspname AS esquema_filho
          , tab_filho.relname AS tabela_filho
          , esq_pai.nspname ||'.'|| tab_pai.relname AS esquema_tabela_pai
          , esq_filho.nspname || '.' || tab_filho.relname AS esquema_tabela_filho
       FROM pg_class AS tab_pai
 INNER JOIN pg_constraint
         ON tab_pai.oid = pg_constraint.confrelid
 INNER JOIN pg_class AS tab_filho
         ON pg_constraint.conrelid = tab_filho.oid
  LEFT JOIN pg_catalog.pg_namespace esq_pai
         ON esq_pai.oid = tab_pai.relnamespace       
  LEFT JOIN pg_catalog.pg_namespace esq_filho
         ON esq_filho.oid = tab_filho.relnamespace           
      WHERE 1=1
        AND tab_pai.relkind = 'r'
   ORDER BY esq_pai.nspname ASC,
            tab_pai.oid ASC  
);
COMMENT ON VIEW vw_depend_tables IS '
Autor: O Peregrino
View que mostra as dependencias entre as tabelas
--SELECT * FROM vw_depend_tables;'
;


--view que mostra as chaves estrangeiras, constraints
DROP VIEW IF EXISTS vw_list_fk CASCADE;
CREATE OR REPLACE VIEW vw_list_fk AS
(
     SELECT
            c.oid
          , n.nspname AS esquema
          , c.relname AS tabela
          , SUBSTRING(consrc,'\\((.*?)\\)') AS nome_atributo
          , SUBSTRING(consrc,'\REFERENCES (.*?)\\(') AS nome_esquema_tabela_referencia
          , conname AS nome_constraint
          , consrc AS conteudo_constraint
       FROM
            (
              SELECT conname,
                     pg_catalog.pg_get_constraintdef(oid) AS consrc,
                     conrelid as relid
                FROM pg_catalog.pg_constraint
               WHERE contype = 'f'
               --AND pg_catalog.pg_get_constraintdef(oid) ILIKE '%tabela%'
            ) AS subq
       JOIN pg_catalog.pg_class c
         ON relid = c.oid
       JOIN pg_namespace AS n
         ON n.oid = c.relnamespace       
);
COMMENT ON VIEW vw_list_fk IS '
Autor: O Peregrino
View que mostra as chaves estrangeiras, constraints das tabelas
--SELECT * FROM vw_list_fk;'
;

--view que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela
DROP VIEW IF EXISTS vw_find_pk_not_first;
CREATE OR REPLACE VIEW vw_find_pk_not_first AS
(
     SELECT
            nspname AS esquema
          , relname AS tabela
          , pg_attribute.attname AS nome_atributo
          , attnum AS ordem_atributo_tabela
       FROM pg_index
          , pg_class
          , pg_attribute
          , pg_namespace
      WHERE pg_namespace.oid = pg_class.relnamespace
        AND indrelid = pg_class.oid
        AND pg_attribute.attrelid = pg_class.oid
        AND pg_attribute.attnum = ANY(pg_index.indkey)
        AND indisprimary
        AND attrelid = pg_class.oid
        AND attnum > 1
        AND pg_class.oid IN (
                             SELECT oid
                               FROM pg_catalog.pg_class
                              WHERE relkind = 'r'
                                AND nspname NOT IN (
                                                         'pg_catalog'
                                                       , 'information_schema'
                                                       , 'pg_toast'
                                                   )
                                                 
                            )
     ORDER BY esquema ASC
            , tabela ASC

);
COMMENT ON VIEW vw_find_pk_not_first IS '
Autores: O Peregrino (view) / José Flávio DBA |zflavio at gmail.com| (query)
View que lista as tabelas cuja a chave primaria nao e o primeiro campo da tabela
--SELECT * FROM vw_find_pk_not_first;'
;


--view que efetua a mesma visualizacao de view vw_find  com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco
DROP VIEW IF EXISTS vw_find_dml;
CREATE OR REPLACE VIEW vw_find_dml AS
(
     SELECT   x.esquema
            , x.tabela
            , x.dono
            , x.comentario
            , x.registros
            , x.tamanho_sem_indices
            , x.tamanho_com_indices
            , x.total_de_leituras_sequencial
            , x.total_de_leituras_indexada
            , x.total_de_leituras
            , x.total_de_inserts
            , x.total_de_updates
            , x.total_de_deletes
       FROM (
                 SELECT *
                   FROM vw_find
             INNER JOIN
                   (
                     SELECT relname AS tabelax
                          , SUM(seq_scan) AS total_de_leituras_sequencial
                          , SUM(idx_scan) AS total_de_leituras_indexada
                          , SUM(seq_scan+idx_scan) AS total_de_leituras
                          , SUM(n_tup_ins) AS total_de_inserts
                          , SUM(n_tup_upd) AS total_de_updates
                          , SUM(n_tup_del) AS total_de_deletes
                       FROM pg_stat_user_tables
                   GROUP BY relname
                   ) AS psut
                ON psut.tabelax = vw_find.tabela
            ) AS x
);
COMMENT ON VIEW vw_find_dml IS '
Autor: O Peregrino
View que efetua a mesma visualizacao de view vw_find  com acrescimo total de leituras de SELECTs, INSERTs, UPDATEs, DELETEs, efetuado nas tabela do banco
--SELECT * FROM vw_find_dml;'
;

--view que mostra os dominios
DROP VIEW IF EXISTS vw_list_domain CASCADE;
CREATE OR REPLACE VIEW vw_list_domain AS
(
SELECT n.nspname as esquema
     , t.typname as nome_dominio
     , pg_catalog.format_type(t.typbasetype, t.typtypmod) as formato_tipo_dominio,
       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault
            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'
            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault
            ELSE ''
       END as modifier,
       pg_catalog.array_to_string(ARRAY(
         SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid
       ), ' ') as check
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'd'
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2
);
COMMENT ON VIEW vw_list_domain IS '
Autor: O Peregrino
View que mostra os dominios cadastrados no banco
SELECT * FROM vw_list_domain;
';

DROP VIEW IF EXISTS vw_list_dict_data CASCADE;
CREATE OR REPLACE VIEW vw_list_dict_data AS
(
     SELECT
            at.esquema
          , at.tabela
          , at.esquema
            || '.'
            || at.tabela AS esquema_tabela
          , at.ordem_atributo_tabela
          , at.nome_atributo
          , at.formato_tipo_atributo
          , at.nao_nulo 
          , at.comentario_coluna
          , CASE WHEN (fk.nome_esquema_tabela_referencia IS NOT NULL) THEN
                    'TABELA: ' ||  fk.nome_esquema_tabela_referencia
                 WHEN (dn.nome_dominio IS NOT NULL) THEN
                    'LISTA: ' || dn.modifier || dn.check
            END AS dominio
          , CASE WHEN (pk.nome_atributo IS NOT NULL) THEN
               'X'
            END AS pri
          , CASE WHEN (fk.nome_atributo IS NOT NULL) THEN
               'X'
            END AS est         
          , CASE WHEN (unq.nome_atributo IS NOT NULL) THEN
               'X'
            END AS can
          , at.padrao
          , ck.conteudo AS check          
       FROM vw_find_attrib at
  LEFT JOIN vw_list_pk pk
         ON (
             at.esquema = pk.esquema
             AND at.tabela = pk.tabela
             AND at.nome_atributo = pk.nome_atributo
            )
  LEFT JOIN vw_list_check ck
         ON (
             at.esquema = ck.esquema
             AND at.tabela = ck.tabela
             AND at.nome_atributo = ck.nome_atributo
            )
  LEFT JOIN vw_list_unique unq
         ON (
             at.esquema = ck.esquema
             AND at.tabela = unq.tabela
             AND at.nome_atributo = unq.nome_atributo
            )         
  LEFT JOIN vw_list_fk fk
         ON (
             at.esquema = fk.esquema
             AND at.tabela = fk.tabela
             AND at.nome_atributo = fk.nome_atributo
            )         
  LEFT JOIN vw_list_domain dn
         ON ( at.formato_tipo_atributo  = dn.nome_dominio )
      WHERE 1=1
   ORDER BY at.esquema ASC
          , at.tabela ASC
          , at.ordem_atributo_tabela ASC
);
COMMENT ON VIEW vw_list_dict_data IS '
Autor: O Peregrino
View que lista o dicionario de dados de toda a base de dados
SELECT * FROM vw_list_dict_data;
';


DROP VIEW IF EXISTS vw_list_dict_data_docx CASCADE;
CREATE OR REPLACE VIEW vw_list_dict_data_docx AS
(
     SELECT
            esquema
          , tabela
          , esquema_tabela AS "Esquema.Tabela"
          , ordem_atributo_tabela
          , nome_atributo AS "Atributo"
          , CASE WHEN (formato_tipo_atributo = 'integer') THEN
                    'inteiro'
                 WHEN (formato_tipo_atributo = 'smallint') THEN
                    'inteiro_pesqueno'
                 WHEN (formato_tipo_atributo = 'bigint') THEN   
                    'inteiro_grande'
                 WHEN (formato_tipo_atributo = 'numeric') THEN                   
                    'numerico'
                 WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN
                    'numerico' || SUBSTRING(formato_tipo_atributo FROM 8)              
                 WHEN (formato_tipo_atributo = 'real') THEN
                    'real'
                 WHEN (formato_tipo_atributo = 'double precision') THEN
                    'dapla_precisao'                                      
                 WHEN (formato_tipo_atributo = 'date') THEN
                    'data'                  
                 WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN
                    'data_hora'
                 WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN
                    'data_hora'  
                 WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN
                    'data_hora'
                 WHEN (formato_tipo_atributo = 'time without time zone') THEN
                    'hora'
                 WHEN (formato_tipo_atributo = 'time with time zone') THEN
                    'hora'                  
                 WHEN (formato_tipo_atributo = 'text') THEN
                    'texto'
                 WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN
                    'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18)
                 WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN
                    'caracter' || SUBSTRING(formato_tipo_atributo FROM 10)                  
                 WHEN (formato_tipo_atributo = '"char"') THEN
                    'caracter'
                 WHEN (formato_tipo_atributo = 'bpchar') THEN
                    'caracter'
                 WHEN (formato_tipo_atributo = 'boolean') THEN
                    'logico'                  
                 WHEN (formato_tipo_atributo = 'bytea') THEN
                    'objeto_binario_grande'
                 ELSE
                    formato_tipo_atributo
            END AS "Tipo"
          , CASE WHEN (nao_nulo IS TRUE) THEN
               'X'
            END AS "Não Nulo"        
          , comentario_coluna AS "Descrição"
          , dominio AS "Dominio"
          , pri AS "PRI"
          , est AS "EST"        
          , can AS "CAN"
          , CASE WHEN (padrao = 'falsevalue()') THEN
                    'falso'
                 WHEN (padrao = 'false') THEN
                    'falso'
                 WHEN (padrao = 'truevalue()') THEN
                    'verdadeiro'
                 WHEN (padrao = 'true') THEN
                    'verdadeiro'
                 WHEN (padrao = 'now()') THEN
                    'agora()'
                 WHEN (padrao = '(\'now\'::text)::date') THEN
                    'agora() :: data'
                 WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN
                    'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8)
                 WHEN (POSITION('::' IN padrao) > 0) THEN
                    SUBSTRING(padrao,'(.*?)\\::')                                
                 ELSE
                    padrao                  
             END AS "Padrão"
          , "check" AS "Check"
       FROM vw_list_dict_data
);
COMMENT ON VIEW vw_list_dict_data_docx IS '
Autor: O Peregrino
View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao
SELECT * FROM vw_list_dict_data_docx;
';

--view que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, usa funcao de usuario sp_tradutor, sp_rm_special_chars, sp_comment_domain, verificar dependencias
DROP VIEW IF EXISTS vw_list_dict_data_doc;
CREATE OR REPLACE VIEW vw_list_dict_data_doc AS
(
     SELECT
            esquema
          , tabela
          , ordem_atributo_tabela         
          , "Esquema.Tabela"
          , "Atributo"
          , "Tipo"
          , "Não Nulo"
          , "Descrição"
          , CASE WHEN "Domínio" IS NOT NULL THEN
               "Domínio" || CHR(10) || 'DOMÍNIO DISCRETO: ' || COALESCE(sp_comment_domain("Descrição"),'')
            ELSE
               COALESCE(sp_comment_domain("Descrição"),'')
            END AS "Domínio"
          , "PRI"
          , "EST"
          , "CAN"
          , "Padrão"
          , "Check"
       FROM
          (


             SELECT
                    esquema
                  , tabela
                  , esquema_tabela AS "Esquema.Tabela"
                  , ordem_atributo_tabela
                  , nome_atributo AS "Atributo"
                  , CASE WHEN (formato_tipo_atributo = 'integer') THEN
                            'inteiro'
                         WHEN (formato_tipo_atributo = 'smallint') THEN
                            'inteiro_pesqueno'
                         WHEN (formato_tipo_atributo = 'bigint') THEN   
                            'inteiro_grande'
                         WHEN (formato_tipo_atributo = 'numeric') THEN                   
                            'numerico'
                         WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 8) = 'numeric(') THEN
                            'numerico' || SUBSTRING(formato_tipo_atributo FROM 8)              
                         WHEN (formato_tipo_atributo = 'real') THEN
                            'real'
                         WHEN (formato_tipo_atributo = 'double precision') THEN
                            'dapla_precisao'                                      
                         WHEN (formato_tipo_atributo = 'date') THEN
                            'data'                  
                         WHEN (formato_tipo_atributo = 'timestamp without time zone') THEN
                            'data_hora'
                         WHEN (formato_tipo_atributo = 'timestamp with time zone') THEN
                            'data_hora'  
                         WHEN (formato_tipo_atributo = 'timestamp(0) without time zone') THEN
                            'data_hora'
                         WHEN (formato_tipo_atributo = 'time without time zone') THEN
                            'hora'
                         WHEN (formato_tipo_atributo = 'time with time zone') THEN
                            'hora'                  
                         WHEN (formato_tipo_atributo = 'text') THEN
                            'texto'
                         WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 17) = 'character varying') THEN
                            'v_caracter' || SUBSTRING(formato_tipo_atributo FROM 18)
                         WHEN (SUBSTRING(formato_tipo_atributo FROM 1 FOR 10) = 'character(') THEN
                            'caracter' || SUBSTRING(formato_tipo_atributo FROM 10)                  
                         WHEN (formato_tipo_atributo = '"char"') THEN
                            'caracter'
                         WHEN (formato_tipo_atributo = 'bpchar') THEN
                            'caracter'
                         WHEN (formato_tipo_atributo = 'boolean') THEN
                            'logico'                  
                         WHEN (formato_tipo_atributo = 'bytea') THEN
                            'objeto_binario_grande'
                         ELSE
                            formato_tipo_atributo
                    END AS "Tipo"
                  , CASE WHEN (nao_nulo IS TRUE) THEN
                       'X'
                    END AS "Não Nulo"        
                  , sp_rm_special_chars(comentario_coluna) AS "Descrição"
                  , dominio AS "Domínio"
                  , pri AS "PRI"
                  , est AS "EST"        
                  , can AS "CAN"
                  , CASE WHEN (padrao = 'falsevalue()') THEN
                            'falso'
                         WHEN (padrao = 'false') THEN
                            'falso'
                         WHEN (padrao = 'truevalue()') THEN
                            'verdadeiro'
                         WHEN (padrao = 'true') THEN
                            'verdadeiro'
                         WHEN (padrao = 'now()') THEN
                            'agora()'
                         WHEN (padrao = '(\'now\'::text)::date') THEN
                            'agora() :: data'
                         WHEN (SUBSTRING(padrao FROM 1 FOR 7) = 'nextval') THEN
                            'proximo_valor_sequencia'||SUBSTRING(SUBSTRING(padrao,'(.*?)\\::')||')' FROM 8)
                         WHEN (POSITION('::' IN padrao) > 0) THEN
                            SUBSTRING(padrao,'(.*?)\\::')                                
                         ELSE
                            padrao                  
                     END AS "Padrão"
                  ,  sp_tradutor("check") AS "Check"
               FROM vw_list_dict_data
          ) AS z
);
COMMENT ON VIEW vw_list_dict_data_doc IS '
Autor: O Peregrino
View que lista o dicionario de dados de toda a base de dados extendido, apropriado para documentacao, esta função sp_tradutor, sp_rm_special_chars, sp_comment_domain, ver dependencias.
SELECT * FROM vw_list_dict_data_doc;
';

--view que lista a ordem de criação das tabelas.
DROP VIEW IF EXISTS  vw_order_tables_created;
CREATE OR REPLACE VIEW  vw_order_tables_created AS
(
     SELECT tab_pk.relname AS pk,
            tab_fk.relname AS fk
       FROM pg_class AS tab_pk
 INNER JOIN pg_constraint
         ON tab_pk.oid = pg_constraint.confrelid
 INNER JOIN pg_class AS tab_fk
         ON pg_constraint.conrelid = tab_fk.oid
      WHERE tab_pk.relkind = 'r'
   ORDER BY tab_pk.oid ASC
);
COMMENT ON VIEW vw_order_tables_created IS ' 
Autor: O Peregrino
View que lista a ordem de criação das tabelas.
OBS: Em testes.
SELECT * FROM vw_order_table_created;
'
; 

Nenhum comentário:

Postar um comentário