-
Notifications
You must be signed in to change notification settings - Fork 0
/
functions.sql
90 lines (65 loc) · 1.6 KB
/
functions.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
--FUNCTIONS
--FUNCTION SQL 1
CREATE OR REPLACE FUNCTION func_somar(INTEGER,INTEGER)
RETURNS INTEGER
SECURITY DEFINER
--RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
LANGUAGE SQL
AS $$
SELECT($1) + ($2);
$$;
SELECT func_somar(2,3);
--FUNCTION SQL 2
CREATE OR REPLACE FUNCTION func_somar_2(INTEGER,INTEGER)
RETURNS INTEGER
SECURITY DEFINER
--RETURNS NULL ON NULL INPUT
CALLED ON NULL INPUT
LANGUAGE SQL
AS $$
SELECT COALESCE($1,0) + COALESCE($2,0);
$$;
SELECT func_somar_2(null,10);
--FUNCTION PLPGSQL 1
CREATE OR REPLACE FUNCTION bancos_add(p_numero INTEGER, p_nome VARCHAR, p_ativo BOOLEAN)
RETURNS INTEGER
SECURITY INVOKER
LANGUAGE PLPGSQL
CALLED ON NULL INPUT
AS $$
DECLARE variavel_id INTEGER;
BEGIN
SELECT INTO variavel_id numero
FROM banco
WHERE numero = p_numero;
RETURN variavel_id;
END; $$;
SELECT bancos_add(333, '', null);
SELECT numero, nome, ativo FROM banco WHERE numero = 333;
--FUNCTION PLPGSQL 2
CREATE OR REPLACE FUNCTION bancos_add_2(p_numero INTEGER, p_nome VARCHAR, p_ativo BOOLEAN)
RETURNS INTEGER
SECURITY INVOKER
LANGUAGE PLPGSQL
CALLED ON NULL INPUT
AS $$
DECLARE variavel_id INTEGER;
BEGIN
IF p_numero IS NULL OR p_nome IS NULL OR p_ativo IS NULL THEN
RETURN 0;
END IF;
SELECT INTO variavel_id numero
FROM banco
WHERE numero = p_numero;
IF variavel_id IS NULL THEN
INSERT INTO banco(numero, nome, ativo)
VALUES (p_numero, p_nome, p_ativo);
END IF;
SELECT INTO variavel_id numero
FROM banco
WHERE numero = p_numero;
RETURN variavel_id;
END; $$;
SELECT bancos_add_2(331615, 'Banco Novo XYZ', true);
SELECT numero, nome, ativo FROM banco WHERE numero = 331615;