-
Notifications
You must be signed in to change notification settings - Fork 10
/
init.postgresql.1table.fn.sql
113 lines (103 loc) · 3.47 KB
/
init.postgresql.1table.fn.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
CREATE UNLOGGED TABLE clientes (
id INTEGER GENERATED BY DEFAULT AS IDENTITY,
saldo INTEGER NOT NULL DEFAULT 0,
extrato jsonb NOT NULL DEFAULT '[]'::jsonb
);
INSERT INTO clientes(id) VALUES (1), (2), (3), (4), (5);
CREATE EXTENSION pg_prewarm;
SELECT pg_prewarm('clientes');
CREATE TYPE faermanj_result AS (
status_code INT,
body jsonb
);
CREATE OR REPLACE FUNCTION proc_transacao(p_cliente_id INT, p_valor INT, p_tipo CHAR, p_descricao CHAR(10))
RETURNS faermanj_result AS $$
DECLARE
v_saldo INT;
n_saldo INT;
v_limite INT;
transaction_result jsonb;
BEGIN
-- Set limit based on client 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;
-- Lock the row for update and get the current balance
SELECT saldo INTO v_saldo FROM clientes WHERE id = p_cliente_id FOR UPDATE;
-- Calculate new balance based on transaction type
IF p_tipo = 'd' THEN
n_saldo := v_saldo - p_valor;
-- Check if new balance is below the limit
IF (n_saldo < (-1 * v_limite)) THEN
-- Transaction would exceed the limit, return error
RETURN ROW(
422,
'{"erro": "Saldo insuficiente"}'::jsonb
)::faermanj_result;
END IF;
ELSE
n_saldo := v_saldo + p_valor;
END IF;
-- Update customer record with new balance and transaction in the statement
UPDATE clientes
SET saldo = n_saldo,
extrato = CASE
WHEN jsonb_array_length(extrato) > 10
THEN jsonb_build_array(
jsonb_build_object(
'valor', p_valor,
'tipo', p_tipo,
'descricao', p_descricao
)
) || extrato #- '{-1}'
ELSE jsonb_build_array(
jsonb_build_object(
'valor', p_valor,
'tipo', p_tipo,
'descricao', p_descricao
)
) || extrato
END
WHERE id = p_cliente_id;
-- Return the result as a faermanj_result type directly
RETURN ROW(
200,
json_build_object(
'saldo', n_saldo,
'limite', v_limite
)::jsonb
)::faermanj_result;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION proc_extrato(p_cliente_id int)
RETURNS faermanj_result AS $$
BEGIN
RETURN (
SELECT ROW(
200,
json_build_object(
'saldo', json_build_object(
'total', saldo,
'data_extrato', '1980-01-09 16:20:00.000000',
'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
),
'ultimas_transacoes', extrato
)
)::faermanj_result
FROM clientes
WHERE id = p_cliente_id
);
END;
$$ LANGUAGE plpgsql;