Skip to content

SQL Queries Úteis

Jump to bottom
Anderson Marinho Pontes de Barros edited this page Apr 10, 2025 · 28 revisions

SQL Queries Úteis

Identificação de Usuários com Órgãos e Lotações

-- Considerando um usuário 'José da Silva', vamos procurar com ILIKE '%jose%silva%' sem acentos
SELECT
  dpp.sesb_pessoa || dpp.matricula "Código"
 ,dpp.nome_pessoa
 ,cpou.sigla_orgao_usu_completa
 ,dpl.sigla_lotacao
FROM dp_pessoa dpp
  INNER JOIN cp_identidade cpi on cpi.id_pessoa = dpp.id_pessoa
  INNER JOIN dp_lotacao dpl ON dpl.id_lotacao = dpp.id_lotacao
  INNER JOIN cp_orgao_usuario cpou ON cpou.id_orgao_usu = dpl.id_orgao_usu
WHERE dpp.nome_pessoa_ai ILIKE '%jose%silva%'
  AND cpi.his_ativo = 1 -- Ativos
  AND dpp.data_fim_pessoa is null -- Não expirados

Alterar senha de usuários

  • Shellscript para gerar senha
# Considerando a senha '123456':

$ echo -n '123456' | md5sum | cut -d' ' -f1

# e10adc3949ba59abbe56e057f20f883e
  • Assumindo que o usúario "postgres" é o usuário de acesso ao sistema, executar:
ALTER ROLE <usuario-aplicacao> SET search_path TO "corporativo", "siga", "sigawf", "public";
ALTER ROLE <usuario-de-quem-ta-executando-o-script> SET search_path TO "corporativo", "siga", "sigawf", "public"; -- Desconecta e conecta de novo para pegar a atualização do usuário
  • SQL para atualizar hashes das senhas no banco de dados (por código)
UPDATE cp_identidade
SET senha_identidade = 'e10adc3949ba59abbe56e057f20f883e' -- HASH MD5 DE '123456'
WHERE id_pessoa IN (
  SELECT p.id_pessoa
  FROM dp_pessoa p
  WHERE (p.sesb_pessoa || p.matricula) IN ('MAT123456') -- MATRÍCULA
    AND p.id_pessoa = id_pessoa
)
  • SQL para atualizar hashes das senhas no banco de dados (todos)
UPDATE cp_identidade
SET senha_identidade = 'e10adc3949ba59abbe56e057f20f883e' -- HASH MD5 DE '123456'
-- Script padrão para banco de homologação com senha utilizada pelo PDS:

UPDATE cp_identidade
SET senha_identidade = '438504a66514500d00314df6bd4cb2c4' -- HASH MD5 DE 'c0d4t4-pbd0c-hml'

Buscar Documento pelo seu código interno LOT-TPD-AAAA/NNNNN 

SELECT
  cpou.sigla_orgao_usu || '-'
    || exfd.sigla_forma_doc || '-'
    || TO_CHAR(exd.dt_reg_doc, 'YYYY') || '/'
    || LPAD(exd.num_expediente::TEXT, GREATEST(LENGTH(exd.num_expediente::TEXT), 5), '0') "numero_processo"
 ,exd.id_doc
 ,'DOCUMENTOS/'
    || cpou.sigla_orgao_usu || '/'
    || exfd.sigla_forma_doc || '/'
    || exd.ano_emissao || '/'
    || exd.id_doc::TEXT
    || '.zip' "arquivo_documento"
FROM siga.ex_documento exd
  INNER JOIN siga.ex_forma_documento exfd
    ON exfd.id_forma_doc = exd.id_forma_doc
  INNER JOIN corporativo.dp_lotacao dpl
    ON dpl.id_lotacao = exd.id_lota_titular
  INNER JOIN corporativo.cp_orgao_usuario cpou
    ON cpou.id_orgao_usu = dpl.id_orgao_usu
WHERE
  cpou.sigla_orgao_usu || '-'
    || exfd.sigla_forma_doc || '-'
    || TO_CHAR(exd.dt_reg_doc, 'YYYY') || '/'
    || LPAD(exd.num_expediente::TEXT, GREATEST(LENGTH(exd.num_expediente::TEXT), 5), '0') = 'LOT-TPD-AAAA/NNNNN' -- EX.: "SEG-OFI-2022/00058"

Buscar Anexos de um Documento pelo seu código interno LOT-TPD-AAAA/NNNNN 

SELECT
  cpou.sigla_orgao_usu || '-'
    || exfd.sigla_forma_doc || '-'
    || exd.ano_emissao || '/'
    || LPAD(exd.num_expediente::TEXT, GREATEST(LENGTH(exd.num_expediente::TEXT), 5), '0') "numero_processo"
 ,cpou.sigla_orgao_usu
 ,exfd.sigla_forma_doc
 ,TO_CHAR(exmv.dt_mov, 'YYYY') "ano"
 ,exmv.conteudo_tp_mov
 ,exmv.id_mov
 ,CASE
    WHEN exmv.conteudo_tp_mov IS NOT NULL
    THEN 'ANEXOS/'
      || cpou.sigla_orgao_usu || '/'
      || exfd.sigla_forma_doc || '/'
      || exd.ano_emissao || '/'
      || exmv.id_mov::TEXT
      || '.zip'
  END "arquivo_anexo"
 ,exd.id_doc
 ,'DOCUMENTOS/'
    || cpou.sigla_orgao_usu || '/'
    || exfd.sigla_forma_doc || '/'
    || exd.ano_emissao || '/'
    || exd.id_doc::TEXT
    || '.zip' "arquivo_documento"
FROM ex_movimentacao exmv
  INNER JOIN dp_lotacao dpl
    ON dpl.id_lotacao = exmv.id_lota_titular
  INNER JOIN cp_orgao_usuario cpou
    ON cpou.id_orgao_usu = dpl.id_orgao_usu
  INNER JOIN ex_mobil exmb
    ON exmb.id_mobil = exmv.id_mobil
  INNER JOIN ex_documento exd
    ON exd.id_doc = exmb.id_doc
  INNER JOIN ex_forma_documento exfd
    ON exfd.id_forma_doc = exd.id_forma_doc
WHERE
  cpou.sigla_orgao_usu || '-'
    || exfd.sigla_forma_doc || '-'
    || exd.ano_emissao || '/'
    || LPAD(exd.num_expediente::TEXT, GREATEST(LENGTH(exd.num_expediente::TEXT), 5), '0') = 'LOT-TPD-AAAA/NNNNN' -- EX.: "SEG-OFI-2022/00058"
  AND exmv.conteudo_tp_mov IS NOT NULL
ORDER BY exmv.id_mov DESC

Buscar usuários/lotações/órgãos com determinada permissão

SELECT
  CASE
    WHEN cpc.id_orgao_usu IS NOT NULL THEN 'ÓRGÃO'
    WHEN cpc.id_lotacao IS NOT NULL THEN 'LOTAÇÃO'
    WHEN (cpc.id_pessoa IS NULL OR cpc.id_identidade IS NULL) THEN 'USUÁRIO'
    ELSE 'OUTRO'
  END "Tipo Permissão"
 ,COALESCE(
    dpp.nome_pessoa
   ,dpl.nome_lotacao
   ,cpou1.nm_orgao_usu
 ) "Nome"
 ,CASE
    WHEN cpc.id_orgao_usu IS NOT NULL THEN cpou1.acronimo_orgao_usu
    WHEN cpc.id_lotacao IS NOT NULL THEN (cpou2.acronimo_orgao_usu || '/' || dpl.sigla_lotacao)
    WHEN (cpc.id_pessoa IS NULL OR cpc.id_identidade IS NULL) THEN COALESCE(cpi1.login_identidade, cpi2.login_identidade)
    ELSE 'OUTRO'
  END "Código"
 ,COALESCE(
    cpou1.nm_orgao_usu
   ,cpou2.nm_orgao_usu
   ,cpou3.nm_orgao_usu
   ,cpou4.nm_orgao_usu
 ) "Órgão"
 ,CASE
    WHEN cpc.id_sit_configuracao = 1 THEN 'PODE'
    WHEN cpc.id_sit_configuracao = 2 THEN 'NÃO PODE'
    WHEN cpc.id_sit_configuracao = 3 THEN 'OBRIGATÓRIO'
    WHEN cpc.id_sit_configuracao = 4 THEN 'OPCIONAL'
    WHEN cpc.id_sit_configuracao = 5 THEN 'DEFAULT'
    WHEN cpc.id_sit_configuracao = 6 THEN 'NÃO DEFAULT'
    WHEN cpc.id_sit_configuracao = 7 THEN 'PROIBIDO'
    WHEN cpc.id_sit_configuracao = 8 THEN 'SÓ LEITURA'
    WHEN cpc.id_sit_configuracao = 9 THEN 'IGNORAR CONFIGURAÇÃO ANTERIOR'
    ELSE 'N/A'
  END "Permissão"
FROM cp_configuracao cpc
  LEFT JOIN cp_identidade cpi1
    ON cpi1.id_identidade = cpc.id_identidade
  LEFT JOIN dp_pessoa dpp
    ON dpp.id_pessoa = cpc.id_pessoa
  LEFT JOIN cp_identidade cpi2
    ON dpp.id_pessoa = cpi2.id_pessoa
  LEFT JOIN cp_orgao_usuario cpou1
    ON cpou1.id_orgao_usu = cpc.id_orgao_usu
  LEFT JOIN dp_lotacao dpl
    ON dpl.id_lotacao = cpc.id_lotacao
  LEFT JOIN cp_orgao_usuario cpou2
    ON cpou2.id_orgao_usu = dpl.id_orgao_usu
  LEFT JOIN cp_orgao_usuario cpou3
    ON cpou3.id_orgao_usu = cpi1.id_orgao_usu
  LEFT JOIN cp_orgao_usuario cpou4
    ON cpou4.id_orgao_usu = cpi2.id_orgao_usu
WHERE
  EXISTS (
    -- QUERY BUSCA SERVIÇO POR SIGLA
    SELECT 1
    FROM cp_servico s
    WHERE s.id_servico = cpc.id_servico
      AND s.sigla_servico ILIKE '%EXEMPLO_CODIGO_PERMISSAO%' -- Ex.: "SIGA-GI-CAD_ORGAO_USUARIO"
  )
  AND cpc.his_dt_fim IS NULL -- PERMISSÃO ATIVA
GROUP BY
  "Tipo Permissão"
 ,"Nome"
 ,"Código"
 ,"Órgão"
 ,"Permissão"
ORDER BY
  "Nome" ASC

[INCONSISTÊNCIA] [BANCO] Fechar todas as identidades para pessoas antigas e criar uma nova para a pessoa mais nova e ativa:

-- IDENTIFICAR TODAS AS PESSOAS QUE TEM IDENTIDADE NOVA FECHADA, MAS COM ALGUMA ANTIGA ABERTA
WITH
"id_iniciais_pessoas_com_alguma_identidade_antiga_aberta" AS (
  SELECT dpp.id_pessoa_inicial "id_inicial"
  FROM dp_pessoa dpp
  WHERE
    dpp.data_fim_pessoa IS NULL
    AND EXISTS (
      SELECT 1
      FROM cp_identidade cpi
      WHERE cpi.his_dt_fim IS NULL
        AND cpi.id_pessoa = dpp.id_pessoa_inicial
        AND cpi.id_pessoa <> dpp.id_pessoa
    )
),
"identidades_fechadas" AS (
  -- FECHA TODAS AS IDENTIDADES ANTIGAS DOS USUÁRIOS INCONSISTENTES
  UPDATE cp_identidade SET
    his_idc_fim = 2
   ,his_dt_fim = NOW()
   ,his_ativo = 0
  WHERE
    his_dt_fim IS NULL
    AND id_pessoa IN (
      -- SELECIONE TODAS AS PESSOAS QUE O ID INICIAL ESTEJA ENTRE OS COM IDENTIDADE ABERTA ANTIGA
      SELECT dpp.id_pessoa
      FROM dp_pessoa dpp
      WHERE
        dpp.id_pessoa_inicial IN (
          SELECT id_inicial
          FROM "id_iniciais_pessoas_com_alguma_identidade_antiga_aberta"
        )
    )
  RETURNING *
)
INSERT INTO cp_identidade (
  id_identidade
 ,id_tp_identidade
 ,id_pessoa
 ,data_criacao_identidade
 ,data_expiracao_identidade
 ,data_cancelamento_identidade
 ,id_orgao_usu
 ,login_identidade
 ,senha_identidade
 ,senha_identidade_cripto
 ,senha_identidade_cripto_sinc
 ,his_id_ini
 ,his_dt_ini
 ,his_idc_ini
 ,his_dt_fim
 ,his_idc_fim
 ,his_ativo
 ,pin_identidade
)
SELECT
  nextval('cp_identidade_id_identidade_seq') -- id_identidade
 ,cpif.id_tp_identidade
 ,(
    SELECT MAX(id_pessoa)
    FROM dp_pessoa dpp
    WHERE dpp.id_pessoa_inicial = (
      SELECT dpp_inicial.id_pessoa_inicial
      FROM dp_pessoa dpp_inicial
      WHERE dpp_inicial.id_pessoa = cpif.id_pessoa
    )
  ) -- cpif.id_pessoa
 ,NOW() -- cpif.data_criacao_identidade
 ,NULL -- cpif.data_expiracao_identidade
 ,NULL -- cpif.data_cancelamento_identidade
 ,cpif.id_orgao_usu
 ,cpif.login_identidade
 ,cpif.senha_identidade
 ,NULL -- cpif.senha_identidade_cripto
 ,NULL -- cpif.senha_identidade_cripto_sinc
 ,cpif.his_id_ini
 ,NOW() -- cpif.his_dt_ini
 ,2 -- cpif.his_idc_ini
 ,NULL -- cpif.his_dt_fim
 ,NULL -- cpif.his_idc_fim
 ,1 -- cpif.his_ativo
 ,NULL -- cpif.pin_identidade
FROM identidades_fechadas cpif

[BANCO] Remover registros de documentos corrompidos.

dada a lista de id_doc de documentos, aplicar as queries abaixo na mesma ordem.

DELETE FROM cp_marca WHERE id_ref IN (
 SELECT id_mobil
 FROM ex_mobil
 WHERE id_doc IN (ids)
);

DELETE FROM ex_movimentacao WHERE id_mobil IN (
 SELECT id_mobil
 FROM ex_mobil
 WHERE id_doc IN (ids)
);

DELETE FROM ex_mobil WHERE id_doc IN (ids);

DELETE FROM ex_documento WHERE id_doc IN (ids);

[BANCO] Exemplo de inativação de lotação setando a data data fim com d-1

UPDATE corporativo.dp_lotacao SET data_fim_lot = (CURRENT_DATE - INTERVAL '1 day')::timestamp + INTERVAL '23:59:00'
where id_lotacao in (
SELECT lot.id_lotacao
FROM corporativo.dp_lotacao lot
LEFT JOIN corporativo.cp_orgao_usuario o on o.id_orgao_usu = lot.id_orgao_usu
WHERE lot.sigla_lotacao = '10BPM' -- sigla da lotacao
AND o.sigla_orgao_usu = 'CPM' -- nome do orgao
AND lot.data_fim_lot is null
)

[BANCO] Exemplo de remoção de registro de usuário duplicado.

-- Lista as pessoas da matricula PCV46188
select id_pessoa, data_ini_pessoa, data_fim_pessoa from corporativo.dp_pessoa where sesb_pessoa = 'PCV' and matricula = '46188';

-- ATENÇÃO! Só Executar o script abaixo se a listagem acima retornar DOIS registros ativos (dt_fim_pessoa = null)
-- Obtém a menor data_ini_pessoa (data de cadastro) de DP_Pessoa da matricula PCV6188.  
-- A ideia é desabilitar o registro com a data de cadastro mais antiga
WITH MinData AS (
    SELECT MIN(data_ini_pessoa) as min_data
    FROM corporativo.dp_pessoa
    WHERE sesb_pessoa = 'PCV'
    AND matricula = '46188'
    AND data_fim_pessoa IS NULL
)
--  Desabilita o registro encontrado  com a data de hoje
UPDATE corporativo.dp_pessoa
SET data_fim_pessoa = current_date
FROM MinData
WHERE data_ini_pessoa = MinData.min_data
AND sesb_pessoa = 'PCV'
AND matricula = '46188';

[BANCO] Exemplo de auditória de inativação de um usuário

select distinct 
p.his_idc_fim id_usuario_que_cancelou,
p.data_fim_pessoa,
p.id_pessoa, 
ip.login_identidade,
i.id_pessoa id_pessoa_alterou,
pp.nome_pessoa as nome_pessoa_que_alterou,
p.* from corporativo.dp_pessoa p

	join corporativo.cp_identidade i
	on i.id_identidade = p.his_idc_fim

	join corporativo.cp_identidade ip
	on ip.id_pessoa = p.id_pessoa

	join corporativo.dp_pessoa pp
	on pp.id_pessoa = i.id_pessoa
	
where
p.cpf_pessoa = 08396206406
-- and  p.his_idc_fim is not null -- = 165239
order by p.data_fim_pessoa desc nulls last

[BANCO] Exemplo de inserção de usuário em lotação

WITH next_id AS (
    SELECT nextval('corporativo.dp_pessoa_id_pessoa_seq') AS id_pessoa
)
INSERT INTO corporativo.dp_pessoa (
    id_pessoa, data_ini_pessoa, cpf_pessoa, nome_pessoa, data_nasc_pessoa,
    matricula, id_lotacao, id_cargo, sesb_pessoa, email_pessoa,
    situacao_funcional_pessoa, id_orgao_usu, ide_pessoa, id_pessoa_inicial,
    nome_exibicao, visivel_tramitacao, tramitar_outros_orgaos
)
SELECT
    id_pessoa, '2024-03-22 13:21:08.654000 +00:00', 9552292433, 'ANDERSON MARINHO PONTES DE BARROS', '1991-06-19 03:00:00.000000 +00:00',
    id_pessoa + 10000, 20428, 14912, 'COD', '[email protected]',
    '1', 190401, id_pessoa + 10000, id_pessoa,
    'ANDERSON MARINHO PONTES DE BARROS', true, true
FROM next_id;

[BANCO] Exemplo de correção de páginação de um processo

-- Verificação dos documentos envolvidos
select * from siga.ex_documento ed where id_doc = 6054716;
select * from siga.ex_movimentacao em;
select * from siga.ex_mobil em where id_doc = 6054716;

-- Atualização da paginação
UPDATE siga.ex_mobil
SET dnm_num_primeira_pagina = CASE
   WHEN id_mobil = 11696389 THEN 1
   WHEN id_mobil = 12181049 THEN 32
   WHEN id_mobil = 12271938 THEN 259
   ELSE dnm_num_primeira_pagina
END
WHERE id_mobil IN (11696389, 12181049, 12271938);

[BANCO] Remoção da marca de urgência do documento (Inserir o ID do documento em ID_DOC)

DELETE FROM corporativo.cp_marca 
WHERE id_mov IN (
    SELECT id_mov FROM siga.ex_movimentacao 
    WHERE id_mobil IN (
        SELECT id_mobil FROM siga.ex_mobil 
        WHERE id_doc = ID_DOC 
        AND id_tipo_mobil = 1
    ) 
    AND id_tp_mov = 62 
    AND id_marcador = 1000
) 
AND id_marcador = 1000;

DELETE FROM siga.ex_movimentacao 
WHERE id_mov IN (
    SELECT id_mov FROM siga.ex_movimentacao 
    WHERE id_mobil IN (
        SELECT id_mobil FROM siga.ex_mobil 
        WHERE id_doc = ID_DOC
        AND id_tipo_mobil = 1
    ) 
    AND id_tp_mov = 62 
    AND id_marcador = 1000
);

[BANCO] Exemplo de remoção da marca de assinatura de anexo

DELETE FROM siga.ex_movimentacao
WHERE id_mov = 60694978;

DELETE FROM corporativo.cp_marca
WHERE id_marca = 91735475;

Links Úteis

Arquivos

Clone this wiki locally