jonathanalves

/*
	Written by:		patpawlowski
	Created On:		Oct 26, 2015 at 4:51:52 PM
        Description:            This is a rough attempt to create a funciton to strip the markup from 
                                    the Act TBL_NOTE.NOTETEXT field. It appears to work for what I need
                                    but could probably use some work with the escaped characters. 

                                It's not particularly fast but it is faster than other solutions I've come 
                                    across. It takes about 4 seconds to parse 2700 records. 

jonathanalves

-- http://tatiyants.com/pev/

EXPLAIN (COSTS OFF)

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

-- realizar a analise da query lenta
explain (analyze,buffers,timing) DELETE FROM indicador_pessoal_versao where id = 'c5df0553-612c-4a89-9d48-131890efff92' 

-- https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/

jonathanalves

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

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

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

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);