Revisão do Modulo PostgreSQL

SELECT name, setting From pg_settings;
ou
SHOW [parametro]

Comandos para o binário psql
psql -U postgres -> Acessa o psql atraves do usuário padrão do postgre
​
psql -U nome_usuario password senha_usuario -> Acessa o psql através de login e senha
​
psql -p numero_da_porta -> tenta conectar ao banco de dados pela porta informada
​
psql -h endereco_IP_host -> passa o endereço ip do host
​
Os comandos acima também podem ser combinados:
psql -h localhost -U username databasename

CREATE ROLE name [ [ WITH ] option [ ... ] ]

ALTER ROLE role_specification [ WHIT ] option [ ...]

DROP ROLE role_specification;

CREATE ROLE professores NOCREATEDB NOCREATEROLE INHERIT NOLOGIN NOBYPASSRLS CONNECTION LIMIT 10;
Alter ROLE professores PASSWORD '123';
CREATE ROLE daniel LOGIN PASSWORD '123';
DROP ROLE daniel;
CREATE ROLE daniel LOGIN PASSWORD '123' IN ROLE professores;
DROP ROLE daniel;
CREATE Role daniel LOGIN PASSWORD '123' ROLE professores;
DROP ROLE daniel;

REVOKE [role que será revogada] FROM [role que terá suas permissões revogadas]
Exemplo:
REVOKE professores FROM daniel;

GRANT [role a ser concedida] TO [role a assumir as permissões]
Exemplo:
GRANT professores TO daniel;

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
 ON DATABASE database_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schema_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
 [, ...] | ALL [ PRIVILEGES ] }
 ON { [ TABLE ] table_name [, ...]
  | ALL TABLES IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ]
 { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
 ON DATABASE database_name [, ...]
 FROM role_specification [, ...]
 [ CASCADE | RESTRICT ]
​
Revogando em todos os DATABASES:
REVOKE ALL ON ALL DATABASE [database] FROM [role];

REVOKE [ GRANT OPTION FOR ]
 { EXECUTE | ALL [ PRIVILEGES ] }
 ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
  | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
 FROM role_specification [, ...]
 [ CASCADE | RESTRICT ]
​
Revogando em todos os SCHEMAS:
REVOKE ALL ON ALL SCHEMA [schema] FROM [role];

REVOKE [ GRANT OPTION FOR ]
 { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
 [, ...] | ALL [ PRIVILEGES ] }
 ON { [ TABLE ] table_name [, ...]
  | ALL TABLES IN SCHEMA schema_name [, ...] }
 FROM role_specification [, ...]
 [ CASCADE | RESTRICT ]

Revogando em todas as Tableas:
REVOKE ALL ON ALL TABLES IN SCHEMA [schema] FROM [role];

INSERT INTO [nome da tabela] ([campos da tabela,])
 VALUES ([valores de acordo com a ordem dos campos acima,]);
 
 Exemplo com Idempotência:
INSERT INTO agencia (banco_numero,numero,nome)
SELECT 341,1,'Centro da cidade'
WHERE NOT EXISTS (SELECT banco_numero,numero,nome FROM agencia WHERE banco_numero = 341 AND nome = 'Centro da cidade');
​
 Exemplo com ON CONFLICT(Se tiver conflito, não faz nada)
INSERT INTO agencia (banco_numero,numero,nome) VALUES (341,1,'Centro da cidade') ON CONFLICT (banco_numero,numero) DO NOTHING;

UPDATE [nome da tabela] SET
[campo1] = [novo valor do campo1],
[campo2] = [novo valor do campo2],
...
[campoN] = [novo valor do campoN]
[WHERE + condições];
* Sempre utilize o WHERE para especificar o registro a ser alterado.

DELETE FROM [nome da tabela]
[WHERE + condições];
* Sempre utilize o WHERE para especificar o registro a ser alterado.

SELECT [campos da tabela]
FROM [nome da tabela]
[WHERE + condições]
* Como boa pratica, evite sempre que puder o SELECT * em Querys por uma questão de performace

= , > / >= , < / <= , <> / != , LIKE , ILIKE , IN
onde: 
LIKE possui case sensitive
e
ILIKE não possui case sensitive

SELECT numero, nome, ativo FROM banco;
SELECT banco_numero, numero, nome FROM agencia;
SELECT numero, nome, email FROM cliente;
SELECT id, nome FROM tipo_transacao;
SELECt banco_numero, agencia_numero, numero, cliente_numero FROM conta_corrente;
SELECT banco_numero, agencia_numero, cliente_numero FROM cliente_transacoes;
​
SELECT * FROM cliente_transacoes;
​
CREATE TABLE IF NOT EXISTS teste (
 id SERIAL PRIMARY KEY,
 nome VARCHAR(50) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
);
​
DROP TABLE IF EXISTS teste;
​
CREATE TABLE IF NOT EXISTS teste (
 cpf VARCHAR(11) NOT NULL,
 nome VARCHAR(50) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (cpf)
);
​
INSERT INTO teste (cpf, nome, created_at)
VALUES ('22344566712','José Colméia','2019-07-01 12:00:00');
​
-- Inserção para dar erro
INSERT INTO teste (cpf, nome, created_at)
VALUES ('22344566712','José Colméia','2019-07-01 12:00:00');
​
INSERT INTO teste (cpf, nome, created_at)
VALUES ('22344566712','José Colméia','2019-07-01 12:00:00') ON CONFLICT (cpf) DO NOTHING;
​
UPDATE teste SET nome = 'Pedro Alvares' WHERE cpf = '22344566712';
​
SELECT * FROM teste;

CREATE DATABASE name
 [ [ WITH ] [ OWNER [=] user_name ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ LC_COLLATE [=] lc_collate ]
   [ LC_CTYPE [=] lc_ctype ]
   [ TABLESPACE [=] tablespace_name ]
   [ ALLOW_CONNECTIONS [=] allowconn ]
   [ CONNECTION LIMIT [=] connlimit ]
   [ IS_TEMPLATE [=] istemplate ] ]

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
​
Melhores Práticas:
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name ( [
 { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
 | table_constraint
 | LIKE source_table [ like_option ... ] }
 [, ... ]
] )
​
Melhores Práticas: Utilizar o IF NOT EXISTS.
CREATE TABLE IF NOT EXISTS table_name(
column_name data_type [ COLLATE collation ] [ column_constraint [ ... ]
)

ALTER DATABASE name RENAME TO new_name
​
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
 action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
 RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
 RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
 RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
 SET SCHEMA new_schema

DROP DATABASE [ IF EXISTS ] name

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

-- CRIAR UM DATABASE (QUERY TOOL)
CREATE DATABASE financas;
-- CRIAR UMA TABELA NO SCHEMA (QUERY TOOL) (TRABALHAR NO PUBLIC)
CREATE TABLE IF NOT EXISTS banco (
 numero INTEGER NOT NULL,
 nome VARCHAR(50) NOT NULL,
 ativo BOOLEAN NOT NULL DEFAULT TRUE,
 data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (numero)
);
​
CREATE TABLE IF NOT EXISTS agencia (
 banco_numero INTEGER NOT NULL,
 numero INTEGER NOT NULL,
 nome VARCHAR(80) NOT NULL,
 ativo BOOLEAN NOT NULL DEFAULT TRUE,
 data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (banco_numero,numero),
 FOREIGN KEY (banco_numero) REFERENCES banco (numero)
);
​
CREATE TABLE cliente (
 numero BIGSERIAL PRIMARY KEY,
 nome VARCHAR(120) NOT NULL,
 email VARCHAR(250) not null,
 ativo BOOLEAN NOT NULL DEFAULT TRUE,
 data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
​
CREATE TABLE conta_corrente (
 banco_numero INTEGER NOT NULL,
 agencia_numero INTEGER NOT NULL,
 numero BIGINT NOT NULL,
 digito SMALLINT NOT NULL,
 cliente_numero BIGINT NOT NULL,
 ativo BOOLEAN NOT NULL DEFAULT TRUE,
 data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (banco_numero,agencia_numero,numero,digito,cliente_numero)
 FOREIGN KEY (banco_numero,agencia_numero) REFERENCES agencia (banco_numero,numero),
 FOREIGN KEY (cliente_numero) REFERENCES cliente (numero)
);
​
 CREATE TABLE tipo_transacao (
 id SMALLSERIAL PRIMARY KEY,
 nome VARCHAR(50) NOT NULL,
 ativo BOOLEAN NOT NULL DEFAULT TRUE,
 data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
​
CREATE TABLE cliente_transacoes (
 id BIGSERIAL PRIMARY KEY,
 banco_numero INTEGER NOT NULL,
 agencia_numero INTEGER NOT NULL,
 conta_corrente_numero BIGINT NOT NULL,
 conta_corrente_digito SMALLINT NOT NULL,
 cliente_numero BIGINT NOT NULL,
 tipo_transacao_id SMALLINT NOT NULL,
 valor NUMERIC(15,2) NOT NULL,
 data_criacao TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (banco_numero,agencia_numero,conta_corrente_numero,conta_corrente_digito,cliente_numero) REFERENCES conta_corrente(banco_numero,agencia_numero,numero,digito,cliente_numero)
);

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [ , ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

SELECT AVG(campo) FROM table_name;

SELECT COUNT(campo) FROM table_name;

SELECT MAX(campo) FROM table_name;

SELECT MIN(campo) FROM table_name;

SELECT SUM(campo) FROM table_name;

SELECT COUNT(id), tipo_transacao_id
FROM cliente_transacoes
GROUP BY tipo_transacao_id;

SELECT * FROM information_schema.columns 
WHERE table_name = 'banco';
SELECT column_name, data_type FROM information_schema.columns 
WHERE table_name = 'banco';
​
SELECT valor FROM cliente_transacoes;
​
SELECT AVG(valor) FROM cliente_transacoes;
​
SELECT COUNT(numero) 
FROM cliente;
​
-- Código com erro
SELECT COUNT(numero), email
FROM cliente
WHERE email ILIKE 'gmail.com';
​
-- Código correto
SELECT COUNT(numero), email
FROM cliente
WHERE email ILIKE 'gmail.com'
GROUP BY email;
​
SELECT MAX(numero)
FROM cliente;
​
SELECT MIN(numero)
FROM cliente;
​
SELECT MAX(valor)
FROM cliente_transacoes;
​
SELECT MIN(valor)
FROM cliente_transacoes;
​
SELECT MAX(valor), tipo_transacao_id
FROM cliente_transacoes
GROUP BY tipo_transacao_id;
​
SELECT MIN(valor)
FROM cliente_transacoes
GROUP BY tipo_transacao_id;
​
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'banco';
​
SELECT COUNT(id), tipo_transacao_id
FROM cliente_transacoes
GROUP BY tipo_transacao_id;
​
SELECT COUNT(id), tipo_transacao_id
FROM cliente_transacoes
GROUP BY tipo_transacao_id
HAVING COUNT(id) > 150;
​
SELECT SUM(valor)
FROM cliente_transacoes;
​
SELECT SUM(valor), tipo_transacao_id
FROM cliente_transacoes
GROUP By tipo_transacao_id;
​
SELECT SUM(valor), tipo_transacao_id
FROM cliente_transacoes
GROUP BY tipo_transacao_id
ORDER BY tipo_transacao_id ASC;
​
SELECT SUM(valor), tipo_transacao_id
FROM cliente_transacoes
GROUP BY tipo_transacao_id
ORDER BY tipo_transacao_id DESC;

SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
JOIN tabela_2 ON tabela_2.campo = tabela_1.campo

SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
LEFT JOIN tabela_2 ON tabela_2.campo = tabela_1.campo

SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
RIGHT JOIN tabela_2 ON tabela_2.campo = tabela_1.campo

SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
FULL JOIN tabela_2 ON tabela_2.campo = tabela_1.campo

SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
CROSS JOIN tabela_2

SELECT numero, nome FROM banco;
SELECT banco_numero, numero, nome FROM agencia;
SELECT numero, nome FROM cliente;
SELECT banco_numero, agencia_numero, numero, digito, cliente_numero FROM conta_corrente;
SELECT id, nome FROM tipo_transacao;
SELECT banco_numero, agencia_numero, conta_corrente_numero, conta_corrente_digito, cliente_numero, valor FROM cliente_transacoes;
​
SELECT count(1) FROM banco; -- 151
SELECT count(1) FROM agencia; -- 296
​
-- 296
SELECT banco.numero, banco.nome, agencia.numero, agencia.nome
FROM banco
JOIN agencia ON agencia.banco_numero = banco.numero; 
​
SELECT count(banco.numero)
FROM banco
JOIN agencia ON agencia.banco_numero = banco.numero;
​
SELECT banco.numero
FROM banco
JOIN agencia ON agencia.banco_numero = banco.numero
GROUP BY banco.numero;
​
SELECT count(distinct banco.numero)
FROM banco
JOIN agencia ON agencia.banco_numero = banco.numero;
​
SELECT banco.numero, banco.nome, agencia.numero, agencia.nome
FROM banco
LEFT JOIN agencia ON agencia.banco_numero = banco.numero;
​
SELECT agencia.numero, agencia.nome, banco.nome
FROM agencia
RIGHT JOIN banco ON banco.numero = agencia.banco_numero;
​
SELECT agencia.numero, agencia.nome, banco.numero, banco.nome
FROM agencia
LEFT JOIN banco ON banco.numero = agencia.banco_numero;
​
SELECT banco.numero, banco.nome, agencia.numero, agencia.nome
FROM banco
FULL JOIN agencia ON agencia.banco_numero = banco.numero;
​
CREATE TABLE IF NOT EXISTS teste_a (id SERIAL PRIMARY KEY, valor VARCHAR(10)); 
CREATE TABLE IF NOT EXISTS teste_b (id SERIAL PRIMARY KEY, valor VARCHAR(10));
​
INSERT INTO teste_a (valor) VALUES ('teste1');
INSERT INTO teste_a (valor) VALUES ('teste2');
INSERT INTO teste_a (valor) VALUES ('teste3');
INSERT INTO teste_a (valor) VALUES ('teste4');
​
INSERT INTO teste_b (valor) VALUES ('teste_a');
INSERT INTO teste_b (valor) VALUES ('teste_b');
INSERT INTO teste_b (valor) VALUES ('teste_c');
INSERT INTO teste_b (valor) VALUES ('teste_d');
​
SELECT tbla.valor, tblb.valor
FROM teste_a tbla
CROSS JOIN teste_b tblb;
​
DROP TABLE IF EXISTS teste_a;
DROP TABLE IF EXISTS teste_b;
​
SELECT banco.nome,
 agencia.nome,
 conta_corrente.numero,
 conta_corrente.digito,
 cliente.nome
FROM banco
JOIN agencia ON agencia.banco_numero = banco.numero
JOIN conta_corrente 
 -- ON conta_corrente.banco.numero = agencia.banco_numero
 ON conta_corrente.banco_numero = agencia.numero 
 AND conta_corrente.agencia_numero = agencia.numero
JOIN cliente
 ON cliente.numero = conta_corrente.cliente_numero

WITH [nome] AS (
 SELECT (campos,)
 FROM tabela_A
 [WHERE]
), [nome2] AS (
 SELECT (campos,)
 FROM tabela_B
 [WHERE]
)
SELECT [nome1].(campos,),[nome2].(campos,)
FROM [nome1]
JOIN [nome2] ....

SELECT numero, nome FROM banco
SELECT banco_numero, numero, nome FROM agencia;
​
WITH tbl_tmp_banco AS (
 SELECT numero, nome
 FROM banco
)
SELECT numero, nome
FROM tbl_tmp_banco;
​
WITH params AS (
 SELECT 213 AS banco_numero
), tbl_tmp_banco AS (
 SELECT numero, nome
 FROM banco
 JOIN params ON params.banco_numero = banco.numero
)
SELECT numero, nome 
FROM tbl_tmp_banco;
​
SELECT banco.numero, banco.nome
FROM banco
JOIN (
 SELECT 213 AS banco_numero
) params ON params.banco_numero = banco_numero;
​
WITH clientes_e_transacoes AS (
 SELECT cliente.nome AS cliente_nome,
   tipo_transacao.nome AS tipo_transacao_nome,
   cliente_transacoes.valor AS tipo_transacao_valor
 FROM cliente_transacoes
 JOIN cliente ON cliente.numero = cliente_transacoes.cliente_numero
 JOIN tipo_transacao ON tipo_transacao.id = cliente_transacoes.tipo_transacao_id
)
SELECT cliente_nome, tipo_transacao_nome, tipo_transacao_valor
FROM clientes_e_transacoes;
​
WITH params AS (
 SELECT 213 AS banco_numero
), tbl_tmp_banco AS (
 SELECT numero, nome
 FROM banco
 JOIN params ON params.banco_numero = banco.numero
)
SELECT numero, nome 
FROM tbl_tmp_banco;
​
SELECT banco.numero, banco.nome
FROM banco
JOIN (
 SELECT 213 AS banco_numero
) params ON params.banco_numero = banco_numero;
​
WITH clientes_e_transacoes AS (
 SELECT cliente.nome AS cliente_nome,
tipo_transacao.nome AS tipo_transacao_nome,
cliente_transacoes.valor AS tipo_transacao_valor
 FROM cliente_transacoes
 JOIN cliente ON cliente.numero = cliente_transacoes.cliente_numero
 JOIN tipo_transacao ON tipo_transacao.id = cliente_transacoes.tipo_transacao_id
 JOIN banco ON banco.numero = cliente_transacoes.banco_numero AND banco.nome ILIKE '%Itau%'
)
SELECT cliente_nome, tipo_transacao_nome, tipo_transacao_valor
FROM clientes_e_transacoes;

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
 [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
 AS query
 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

*A VIEW "TEMPORARY" só existe durante a sessão do usuário que criou a VIEW
*Como boas práticas sempre devemos utilizar o parâmetro OR REPLACE 
Exemplo:
CREATE OR REPLACE VIEW vw_bancos AS (
 SELECT numero, nome, ativo
 FROM banco
);

CREATE OR REPLACE RECURSIVE VIEW (nome_da_view) (campos_da_view) AS (
 SELECT base
 UNION ALL
 SELECT campos
 FROM tabela_base
 JOIN (nome_da_view)
);

CREATE TABLE IF NOT EXISTS funcionarios (
 id SERIAL NOT NULL,
 nome VARCHAR(50),
 gerente INTEGER,
 PRIMARY KEY (id),
 FOREIGN KEY (gerente) REFERENCES funcionarios (id)
);
​
INSERT INTO funcionarios (nome, gerente) VALUES ('Ancelmo',null);
INSERT INTO funcionarios (nome, gerente) VALUES ('Beatriz',1);
INSERT INTO funcionarios (nome, gerente) VALUES ('Magno',1);
INSERT INTO funcionarios (nome, gerente) VALUES ('Cremilda',2);
INSERT INTO funcionarios (nome, gerente) VALUES ('Wagner',4);
​
CREATE OR REPLACE RECURSIVE VIEW vw_funcionarios (id, gerente, funcionario) AS (
 SELECT id, gerente, nome
 FROM funcionarios
 WHERE gerente IS NULL
 UNION ALL
 SELECT funcionarios.id, funcionarios.gerente, funcionarios.nome
 FROM funcionarios
 JOIN vw_funcionarios ON vw_funcionarios.id = funcionarios.gerente
);
​
SELECT id, gerente, funcionario
FROM vw_funcionarios
​
CREATE OR REPLACE RECURSIVE VIEW vw_funcionarios (id, gerente, funcionario) AS (
 SELECT id, CAST(" AS VARCHAR) AS gerente, nome
 FROM funcionarios
 WHERE gerente IS NULL
 UNION ALL
 SELECT funcionarios.id, funcionarios.gerente, funcionarios.nome
 FROM funcionarios
 JOIN vw_funcionarios ON vw_funcionarios.id = funcionarios.gerente
 JOIN funcionarios gerentes ON gerentes.id = vw_funcionarios.id
);
​
SELECT id, gerente, funcionario
FROM vw_funcionarios

CREATE OR REPLACE VIEW vw_bancos AS (
 SELECT numero, nome, ativo
 FROM banco
 WHERE ativo IS TRUE
);
​
CREATE OR REPLACE VIEW vw_bancos_maiores_que_100 AS (
 SELECT numero, nome, ativo
 FROM vw_banco
 WHERE numero > 100
) WITH LOCAL CHECK OPTION;
​
*Este comando ira retornar um erro pois o campo numero é menor que 100
INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (99, 'Banco DIO', FALSE)
*Este comando ira passar pois o campo numero é maior que 100
INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (200, 'Banco DIO', FALSE))
-- OK

CREATE OR REPLACE VIEW vw_bancos AS (
 SELECT numero, nome, ativo
 FROM banco
 WHERE ativo IS TRUE
);
​
CREATE OR REPLACE VIEW vw_bancos_maiores_que_100 AS (
 SELECT numero, nome, ativo
 FROM vw_banco
 WHERE numero > 100
) WITH CASCADED CHECK OPTION;
​
*Este comando ira retornar um erro pois o campo numero é menor que 100 e o ativo é FALSE
INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (99, 'Banco DIO', FALSE)
​
*Este comando ira retornar um erro pois o campo ativo é FALSE
INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (200, 'Banco DIO', FALSE))
-- ERRO

SELECT numero, nome, ativo
FROM banco;
​
CREATE OR REPLACE VIEW vw_bancos AS (
 SELECT numero, nome, ativo
 FROM banco
);
​
SELECT numero, nome, ativo
FROM vw_bancos;
​
CREATE OR REPLACE VIEW vw_bancos_2 (banco_numero, banco_nome, banco_ativo) AS (
 SELECT numero, nome, ativo
 FROM banco
);
SELECT banco_numero, banco_nome, banco_ativo
FROM vw_bancos_2
​
INSERT INTO vw_bancos_2 (banco_numero, banco_nome, banco_ativo)
VALUES (51, 'Banco Boa Idéia', TRUE);
​
SELECT banco_numero, banco_nome, banco_ativo FROM vw_bancos_2 WHERE banco_numero = 51;
​
SELECT numero, nome, ativo FROM banco WHERE numero = 51;
​
UPDATE vw_bancos_2 SET banco_ativo = FALSE WHERE banco_numero = 51;
​
SELECT banco_numero, banco_nome, banco_ativo FROM vw_bancos_2 WHERE banco_numero = 51;
​
SELECT numero, nome, ativo FROM banco WHERE numero = 51;
​
DELETE FROM vw_bancos_2 WHERE banco_numero = 51;
​
SELECT banco_numero, banco_nome, banco_ativo FROM vw_bancos_2 WHERE banco_numero = 51;
​
SELECT numero, nome, ativo FROM banco WHERE numero = 51;
​
CREATE OR REPLACE TEMPORARY VIEW vw_agencia AS (
 SELECT nome FROM agencia
);
 
SELECT nome FROM vw_agencia;
​
CREATE OR REPLACE VIEW vw_bancos_ativos AS (
 SELECT numero, nome, ativo
 FROM banco
 WHERE ativo IS TRUE
) WITH LOCAL CHECK OPTION;
​
INSERT INTO vw_bancos_ativos (numero, nome, ativo) VALUES (51, 'Banco Boa Idéia', FALSE);
-- ERRO
​
CREATE OR REPLACE VIEW vw_bancos_com_a AS (
 SELECT numero, nome, ativo
 FROM vw_bancos_ativos
 WHERE nome ILIKE 'a%'
) WITH LOCAL CHECK OPTION; 
​
SELECT numero, nome, ativo FROM vw_bancos_com_a;
​
INSERT INTO vw_bancos_com_a (numero, nome, ativo) VALUES (333,'Beta Omega', TRUE);
-- ERRO
​
INSERT INTO vw_bancos_com_a (numero, nome, ativo) VALUES (333,'Alfa Omega', TRUE);
 
INSERT INTO vw_bancos_com_a (numero, nome, ativo) VALUES (331,'Alfa Gama', FALSE);
-- ERRO               
             
CREATE OR REPLACE VIEW vw_bancos_com_a AS (
 SELECT numero, nome, ativo
 FROM vw_bancos_ativos
 WHERE nome ILIKE 'a%'
)WITH CASCADED CHECK OPTION;
​
INSERT INTO vw_bancos_com_a (numero, nome, ativo) VALUES (331,'Alfa Gama Beta', FALSE);
-- ERRO

BEGIN;
​
 UPDATE conta SET valor = - 100.00
 WHERE nome = 'Alice';
​
 UPDATE conta SET valor = + 100.00
 WHERE nome = 'Bob';
​
COMMIT;
​
BEGIN;
 UPDATE conta SET valor = valor - 100.00
 WHERE nome = 'Alice';
​
SAVEPOINT my_savepoint;
 UPDATE conta SET valor = valor + 100.00
 WHERE nome = 'Bob';
 -- oops --- não é para o Bob, é para o Wally!!!
​
ROLLBACK TO my_savepoint;
 UPDATE conta SET valor = valor + 100.00
 WHERE nome = 'Wally';
​
COMMIT;

SELECT numero, nome, ativo FROM banco ORDER BY numero ASC;
​
UPDATE banco SET ativo = false WHERE numero = 0;
​
SELECT numero, nome, ativo FROM banco ORDER BY numero ASC;
​
BEGIN;
UPDATE banco SET ativo = true WHERE numero = 0;
SELECT numero, nome, ativo FROM banco WHERE numero = 0;
ROLLBACK;
​
SELECT numero, nome, ativo FROM banco ORDER BY numero ASC;
​
BEGIN;
UPDATE banco SET ativo = true WHERE numero = 0;
COMMIT;
​
SELECT numero, nome, ativo FROM banco ORDER BY numero ASC;
​
SELECT id, gerente, nome
FROM funcionarios;
​
BEGIN;
UPDATE funcionarios SET gerente = 2 WHERE id = 3;
SAVEPOINT if_func;
UPDATE funcionarios SET gerente = null;
ROLLBACK TO if_func;
UPDATE funcionarios SET gerente = 3 WHERE id = 5;
COMMIT;
​
SELECT id, gerente, nome
FROM funcionarios;

CREATE [ OR REPLACE ] FUNCTION
 name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
 [ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
 { LANGUAGE lang_name
 | TRANSFORM { FOR TYPE type_name } [, ... ]
 | WINDOW
 | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
 | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
 | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
 | PARALLEL { UNSAFE | RESTRICTED | SAFE }
 | COST execution_cost
 | ROWS result_rows
 | SUPPORT support_function
 | SET configuration_parameter { TO value | = value | FROM CURRENT }
 | AS 'definition'
 | AS 'obj_file', 'link_symbol'
 } ...
 
 *Sempre apelando para boas práticas, criar a função com "CREATE OR REPLACE"

CREATE OR REPLACE FUNCTION banco_manage(p_numero INTEGER,p_nome VARCHAR(50),p_ativo BOOLEAN)
RETURNS TABLE (banco_numero INTEGER, banco_nome VARCHAR(50), banco_ativo BOOLEAN)
LANGUAGE PLPGSQL
SECURITY DEFINER
RETURNS NULL ON NULL INPUT
AS $$
BEGIN
 -- O COMANDO ABAIXO VAI REALIZAR O INSERT OU O UPDATE DO BANCO
 -- O COMANDO ON CONFLICT PPODE SER USADO PARA FAZER NADA (DO NOTHING)
 -- OU PARA REALIZAR UM UPDATE NO NOSSO CASO.
 INSERT INTO banco (numero, nome, ativo)
 VALUES (p_numero, p_nome, p_ativo)
 ON CONFLICT (numero) DO UPDATE SET nome = p_nome, ativo = p_ativo;

 -- DEVEMOS RETORNAR UMA TABELA
 -- O RETURN NESTE CASO DEVE SER UMA QUERY
 RETURN QUERY
 SELECT numero, nome, ativo
 FROM banco
 WHERE numero = p_numero;
END; $$;
​
CREATE OR REPLACE FUNCTION agencia_manage(p_banco_numero INTEGER, p_numero INTEGER, p_nome VARCHAR(80), p_ativo BOOLEAN)
RETURNS TABLE (banco_nome VARCHAR, agencia_numero INTEGER, agencia_nome VARCHAR, agencia_ativo BOOLEAN)
LANGUAGE PLPGSQL
SECURITY DEFINER
RETURNS NULL ON NULL INPUT
AS $$
DECLARE variavel_banco_numero INTEGER;
BEGIN
 -- AQUI NÓS VAMOS VALIDAR A EXISTÊNCIA DO BANCO
 -- E PRINCIPALMENTE SE ELE ESTÁ ATIVO
 SELECT INTO variavel_banco_numero numero
 FROM vw_bancos
 WHERE numero = p_banco_numero
 AND ativo IS TRUE;

 -- SE OBTIVERMOS O RETORNO DO COMANDO ACIMA
 -- ENTÃO O BANCO EXISTE E ESTÁ ATIVO
 -- E PODEMOS PROSSEGUIR COM O INSERT DA AGÊNCIA
 IF variavel_banco_numero IS NOT NULL THEN
 -- O COMANDO ABAIXO VAI REALIZAR O INSERT OU O UPDATE DO BANCO
 -- O COMANDO ON CONFLICT PPODE SER USADO PARA FAZER NADA (DO NOTHING)
 -- OU PARA REALIZAR UM UPDATE NO NOSSO CASO.
 -- !!! REPAREM QUE O UPDATE SÓ SERÁ REALIZADO NOS CAMPOS NOME E ATIVO DA AGENCIA !!!
 -- !!! DESAFIO : QUE TAL MELHORAR ESSE CÓDIGO PARA SER POSSÍVEL A TROCA DE BANCO DAS AGÊNCIAS ???
 INSERT INTO AGENCIA (banco_numero, numero, nome, ativo)
 VALUES (p_banco_numero, p_numero, p_nome, p_ativo)
 ON CONFLICT (banco_numero, numero) DO UPDATE SET
 nome = p_nome,
 ativo = p_ativo;
 END IF;
​
 -- DEVEMOS RETORNAR UMA TABELA
 -- O RETURN NESTE CASO DEVE SER UMA QUERY
 RETURN QUERY
 SELECT banco.nome AS banco_nome, 
     agencia.numero AS agencia_numero, 
     agencia.nome AS agencia_nome, 
     agencia.ativo AS agencia_ativo
 FROM agencia
 JOIN banco ON banco.numero = agencia.numero
 WHERE agencia.banco_numero = p_banco_numero
 AND agencia.numero = p_numero;
END; $$;
​
CREATE OR REPLACE FUNCTION cliente_manage(p_numero INTEGER, p_nome VARCHAR(120), p_email VARCHAR(250), p_ativo BOOLEAN)
RETURNS BOOLEAN
LANGUAGE PLPGSQL
SECURITY DEFINER
CALLED ON NULL INPUT
AS $$
BEGIN
 -- VAMOS VALIDAR SOMENTE OS PARÂMETROS MAIS IMPORTANTES
 IF p_numero IS NULL OR p_nome IS NULL THEN
 RETURN FALSE;
 END IF;

 -- FAREMOS O INSERT COM TRATAMENTO DE VALORES NULOS
 INSERT INTO cliente (numero, nome, email, ativo)
 VALUES (p_numero, p_nome, COALESCE(p_email,CONCAT(p_nome,'@sem_email')), COALESCE(p_ativo,TRUE))
 ON CONFLICT (numero) DO UPDATE SET nome = p_nome, email = CONCAT(p_nome,'@sem_email'), ativo = COALESCE(p_ativo,TRUE);

 RETURN TRUE;
END; $$;