jonathanalves

CREATE EXTENSION tablefunc;

CREATE TABLE sales(year int, month int, qty int);
INSERT INTO sales VALUES(2007, 1, 1000);
INSERT INTO sales VALUES(2007, 2, 1500);
INSERT INTO sales VALUES(2007, 7, 500);
INSERT INTO sales VALUES(2007, 11, 1500);
INSERT INTO sales VALUES(2007, 12, 2000);
INSERT INTO sales VALUES(2008, 1, 1000);
INSERT INTO sales VALUES(2009, 5, 2500);

jonathanalves

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;

public class Gzip {

jonathanalves

-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 

jonathanalves

CREATE OR REPLACE FUNCTION jsonb_remove_keys(
	jdata JSONB,
	keys TEXT[]
)
RETURNS JSONB AS $$
DECLARE
	result JSONB;
	len INT;
	target TEXT;

jonathanalves

create table migracao.atos_utilizados (
cartorio varchar,
protocolo_pedido varchar,
pessoa_ato varchar,
data_utilizacao varchar,
retornado varchar,
tipo_ato varchar,
nome_serventuario varchar,
inutilizado varchar,
justificativa_inutilizacao varchar,

jonathanalves

INSERT INTO public.usuario
(id, cadastro, ativo, nome, administrador, alerta_certidao, atendente, digitalizador, financeiro, gestor,  login, painel_atendimento, sangria, senha, valor_caixa, departamento_id)
VALUES(uuid_generate_v4(), CURRENT_TIMESTAMP, true, 'johnny', true, true, true, true, true, true, 'johnny', true, 0, '$2a$10$N1RmIRN4CNwuY7EIfTQhJOnJzFRmNSoa.maqWe2i3fFb6h2I2td8G', 0, 'b8e62c98-f28c-4af7-9620-90696aab89c6');


-- após criação do schema public, antes dos backups, rodar: 

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "unaccent"; 
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; 

jonathanalves

create table migracao.lote as 
select id as imovel, uuid_generate_v4() protocolo, row_number() over() as ordem 
from indicador_real where livro = 'MATRICULA' and status != 'PENDENTE' and codigo in ();

with tempCodigo as (
	select max(codigo) as codigo from protocolo where dominio = 'PROTOCOLO_RI' and tipo_protocolo = 'NORMAL'
)
INSERT INTO protocolo (
	id, codigo, atualizacao, cadastro, cancelamento_automatico, 
	custas, dominio, emolumentos_busca, encerramento, entrega, entregue, exigencias, indices, legado, 

jonathanalves

/* DTO ato ------------------------------------------- */

{

  "valoresBaseCalculo":{

    "alienacao":10000,
    "alienacaoAtualizado":12427.14,

    "valorAvaliacao":10000,

jonathanalves

update livro_protocolo set ordem = null where "data" >= '2020-12-05';

with maximo as (
	select max(ordem) as maximo from livro_protocolo lp 
),
ordens as (
	select lp.id, lp."data", lp.tipo, m.maximo + row_number() over(order by lp."data" asc, p.codigo asc, lp.tipo asc) ordem
	from livro_protocolo lp
	join protocolo p on p.id = lp.protocolo_id 
	join maximo m on 1=1

jonathanalves

ALTER TABLE indicador_pessoal_versao DROP CONSTRAINT uk_2s3c25we0a91ejyy5moa8623j;
       
update indicador_pessoal_versao 
	set hashcode = md5(CONCAT_WS(';',
              coalesce(indicador_Pessoal_Id::varchar, '')
            , coalesce(nome, '')
            , coalesce(documento, '')
            , coalesce(documento_Desconhecido::varchar, '')
            , coalesce(denominacao, '')