fabriziomello

CREATE OR REPLACE PROCEDURE custom_create_chunk(hypertable REGCLASS, trunc_field TEXT DEFAULT 'month') AS
$$
DECLARE
  r RECORD;
  range_start BIGINT;
  range_end BIGINT;
  slices JSONB;
BEGIN

  SELECT

fabriziomello

-- git@github.com:pramsey/pgsql-http.git
CREATE EXTENSION http;
WITH dados AS (
    SELECT
        content::jsonb AS obj, 1::integer AS turno  
    FROM
        http_get('https://resultados.tse.jus.br/oficial/ele2022/544/dados-simplificados/br/br-c0001-e000544-r.json')
    UNION
    SELECT
        content::jsonb AS obj, 2::integer AS turno  

fabriziomello

static_resources:
  listeners:
  - name: postgres_listener
    address:
      socket_address:
        address: 0.0.0.0
        port_value: 5432
    listener_filters:
    - name: envoy.filters.listener.original_src
      typed_config:

fabriziomello

/*
 * search_in_tables
 *
 * Usage example:
 *     SELECT * FROM search_in_tables('public', 'foo', '%pattern%');
 *
 */
CREATE OR REPLACE FUNCTION search_in_tables(                                                                                                                                                                                                                                                                                                                               
  IN  ptable_schema   TEXT,                                                                                                                                                                                                                                                                                                                                                
  IN  ptable_name     TEXT,                                                                                                                                                     

fabriziomello

/*
 *
 *  Desafio do Henrique Bastos ()
 *
 *   Dado uma lista de números e um número k, retorne se quaisquer dois números da lista
 *   somados resultam no número k.
 *
 *   Por exemplo, dado [10, 15, 3, 7] e k = 17, retorne verdadeiro, já que 10 + 7 = 17.
 *
 *   Passe pelos números da lista uma única vez.

fabriziomello

CREATE OR REPLACE FUNCTION bytea_import(filename text) RETURNS bytea AS
$$
    import os
    with open(filename, 'r') as f:
      s = f.read()
    return s
$$
LANGUAGE 'plpythonu' VOLATILE;

fabriziomello

-- https://psutil.readthedocs.io/en/latest/#psutil.Process.memory_full_info
-- Linux: 0=rss, 1=vms, 2=shared, 3=text, 4=lib, 5=data, 6=dirty, 7=uss, 8=pss, 9=swap
CREATE OR REPLACE FUNCTION get_memory_info(pid INTEGER DEFAULT NULL, memtype INTEGER DEFAULT 0)
RETURNS BIGINT
AS
$$
    import psutil
    p = psutil.Process(pid)
    return p.memory_full_info()[memtype]
$$

fabriziomello

CREATE EXTENSION dblink;

-- customize start
CREATE SERVER pgbouncer FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host '172.17.10.95',
                                                                 port '6432',
                                                                 dbname 'pgbouncer');

CREATE USER MAPPING FOR PUBLIC SERVER pgbouncer OPTIONS (user 'postgres');
-- customize stop

fabriziomello

CREATE TABLE public.gapless_sequence (
  sequence_schema TEXT,
  sequence_name TEXT,
  last_value BIGINT DEFAULT 0,
  step INTEGER DEFAULT 1
);

ALTER TABLE public.gapless_sequence
  ADD CONSTRAINT gapless_sequence_pk PRIMARY KEY (sequence_schema, sequence_name);

fabriziomello

ssl = off 
maintenance_work_mem = 2GB 
work_mem = 2GB 
fsync = off 
synchronous_commit = off 
wal_level = minimal
max_wal_senders = 0 
archive_mode = off 
wal_buffers = -1
wal_writer_delay = 20ms