init.postgresql.3tables.sql

CREATE UNLOGGED TABLE clientes (
	id SERIAL PRIMARY KEY,
	saldo INTEGER NOT NULL DEFAULT 0
);
    
CREATE UNLOGGED TABLE transacoes (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	cliente_id INTEGER NOT NULL,
	valor INTEGER NOT NULL,
	tipo CHAR(1) NOT NULL,
	descricao VARCHAR(10) NOT NULL,
	realizada_em TIMESTAMP(6) NOT NULL);

CREATE UNLOGGED TABLE extratos (
	id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	cliente_id INTEGER NOT NULL,
	extrato TEXT NOT NULL,
    realizada_em TIMESTAMP(6) NOT NULL DEFAULT NOW());


CREATE INDEX idx_cliente_id ON transacoes (cliente_id);
CREATE INDEX idx_cliente_id_extratos ON extratos (cliente_id);


CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT pg_prewarm('clientes');
SELECT pg_prewarm('transacoes');

CREATE TYPE json_result AS (
  status_code INT,
  body json
);

CREATE OR REPLACE FUNCTION proc_transacao(p_shard INT, p_cliente_id INT, p_valor INT, p_tipo CHAR, p_descricao CHAR(10))
RETURNS json_result as $$
DECLARE
    diff INT;
    v_saldo INT;
    n_saldo INT;
    v_limite INT;
    result json_result;
    v_extrato TEXT;
BEGIN
    PERFORM pg_advisory_xact_lock(p_cliente_id);
    v_limite := CASE p_cliente_id
        WHEN 1 THEN 100000
        WHEN 2 THEN 80000
        WHEN 3 THEN 1000000
        WHEN 4 THEN 10000000
        WHEN 5 THEN 500000
        ELSE -1 
    END;

    SELECT saldo 
        INTO v_saldo
        FROM clientes
        WHERE id = p_cliente_id
        FOR UPDATE;

    IF (p_tipo = 'd') THEN
        IF (v_saldo - p_valor) < (-1 * v_limite) THEN
            result.body := 'LIMITE_INDISPONIVEL';
            result.status_code := 422;
            RETURN result;
        ELSE
            n_saldo := v_saldo - p_valor;
        END IF;
    ELSE
        n_saldo := v_saldo + p_valor;
    END IF;
    
    INSERT INTO transacoes 
                     (cliente_id,   valor,   tipo,   descricao,      realizada_em)
            VALUES (p_cliente_id, p_valor, p_tipo, p_descricao, now());

    
    SELECT json_build_object(
        'saldo', json_build_object(
            'total', n_saldo,
            'data_extrato', TO_CHAR(now(), 'YYYY-MM-DD HH:MI:SS.US'),
            'limite', v_limite
        ),
        'ultimas_transacoes', COALESCE((
            SELECT json_agg(row_to_json(t)) FROM (
                SELECT valor, tipo, descricao
                FROM transacoes
                WHERE cliente_id = p_cliente_id
                ORDER BY realizada_em DESC
                LIMIT 10
            ) t
        ), '[]')
    ) INTO v_extrato;

    SELECT json_build_object(
        'saldo', n_saldo,
        'limite', v_limite
    ) into result.body;

    UPDATE clientes 
        SET 
            saldo = n_saldo
        WHERE id = p_cliente_id;

    INSERT INTO extratos (cliente_id, extrato) 
        VALUES (p_cliente_id, v_extrato);
    result.status_code := 200;
    RETURN result;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION proc_extrato(p_cliente_id int)
RETURNS json_result AS $$
DECLARE
    result json_result;
    row_count integer;
    v_saldo numeric;
    v_limite numeric;
BEGIN
    PERFORM pg_advisory_xact_lock(p_cliente_id);
    SELECT extrato
        INTO result.body 
        FROM extratos
        WHERE id = p_cliente_id
        ORDER BY realizada_em DESC
        LIMIT 1;

    result.status_code := 200;
    RETURN result;
END;
$$ LANGUAGE plpgsql;


INSERT INTO clientes(id) VALUES 
    (1), (2), (3), (4), (5);

INSERT INTO extratos(cliente_id, extrato) VALUES 
    (1, '{"saldo":{"total":0,"data_extrato":"2000-01-01 00:00:01.00000","limite":100000},"ultimas_transacoes":[]}'), 
    (2, '{"saldo":{"total":0,"data_extrato":"2000-01-01 00:00:01.00000","limite":80000},"ultimas_transacoes":[]}'), 
    (3, '{"saldo":{"total":0,"data_extrato":"2000-01-01 00:00:01.00000","limite":1000000},"ultimas_transacoes":[]}'), 
    (4, '{"saldo":{"total":0,"data_extrato":"2000-01-01 00:00:01.00000","limite":10000000},"ultimas_transacoes":[]}'), 
    (5, '{"saldo":{"total":0,"data_extrato":"2000-01-01 00:00:01.00000","limite":500000},"ultimas_transacoes":[]}');