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