2

Conceitos e Melhores Práticas com Bancos de Dados PostgreSQL - (Material Curso Dio)

#SQL
Anderson Froes
Anderson Froes
Professor: 
Daniel Costa

Link para Download do Artigo:
https://drive.google.com/file/d/1x55eQ3RcdknZSePs3fLGD9Bwtp4i9v9K/view?usp=sharing

Link para Material GitHub: (Professor)
https://github.com/drobcosta/digital_innovation_one

Capitulo 1 - Introdução ao banco de dados PostgreSQL
**Conceitos e melhores práticas com banco de dados PostgreSQL**
**Objetivos da Aula**
- Fundamentos de banco de dados
- Modelo relacional
- Introdução ao PostgreSQL

**Requisitos Básicos**
- Windows, Linux, OSX
- Noções básicas em lógica de programação
- Noções básicas de modelos de dados
- Noções básicas de Linux

**Parte 1: Fundamentos de banco de dados**
**O que são dados?**
- Valores brutos, fatos brutos, observações documentadas, registros soltos, que são recolhidos e armazenados sem sofrer qualquer tratamento.

**O que são informações?**
- Estruturação de dados, organização de dados.
- Conjunto de dados relacionados entre si que geram valor, que criam sentidos aos dados.
- Material do conhecimento.

**Parte 2: Modelos relacional**
**Definição:**
- Modelo mais comum que classifica e organiza as informações em tabelas com linhas e colunas.
- As linhas ou tuplas, são os dados organizados, são os valores das tabelas, e as colunas são os atributos destes dados.

**Tabelas:**
- Conjuntos de dados dispostos em colunas e linhas referentes a um objetivo comum.
- As colunas são consideradas como "campos da tabela", como atributos da tabela.
- As linhas de uma tabela são chamadas também de tuplas, e é onde estão contidos os valores, os dados.

**O que pode ser definido como tabelas?**
- Coisas tangíveis
  - Elementos físicos (carro, produto, animal).
- Funções
  - Perfis de usuário, status de compra.
- Eventos ou ocorrências
  - Produtos de um pedido, histórico de dados.

**Colunas importantes**
- Chave Primária / Primary Key
  - Conjunto de um ou mais campos que nunca se repetem.
  - Identidade da tabela.
  - São utilizados como índice de referência na criação de relacionamentos entre tabelas.
- Chave Estrangeira / Foreign Key / FK
  - Valor de referência a uma PK de outra tabela da mesma tabela para criar um relacionamento.

**Sistemas de gerenciamento de banco de dados**
- Podendo também ser chamado de Sistemas de gestão de base de dados.
- Chamamos pela sigla SGBD.
- Conjunto de programas ou softwares responsáveis pelo gerenciamento de um banco de dados.
- Programas que facilitam a administração de um banco de dados.

**Parte 3: Introdução ao PostgreSQL**
**O que é o PostgreSQL?**
- Sistema de gerenciamento de banco de dados objeto relacional.
- Teve início no Departamento de Ciência da Computação na Universidade da Califórnia em Berkeley em 1986.
- SGBD Opensource.

**Principais características**
- OpenSource.
- Point in time recovery.
- Linguagem procedural com suporte a várias linguagens de programação (perl, python, etc).
- Views, functions, procedures, triggers.
- Consultas complexas e Common table expressions (CTE).
- Suporte a dados geográficos (PostGIS).
- Controle de concorrência multi-versão.

**Instalação e documentação do PostgreSQL**
- Site oficial: https://www.postgresql.org/
- Download: https://www.postgresql.org/download/
- Documentação: https://www.postgresql.org/docs/manuals/

**Instalação pgAdmin**
Ferramenta gráfica para interagir com o banco de dados
- https://www.pgadmin.org/

**Instalação PostgreSQL e pgAdmin no Ubuntu**
**PostgreSQL Instalação**
- Crie um arquivo já configurado no repositório de arquivos = sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
- Importar a chave do repositório oficial = wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
- Update de package list = sudo apt-get update
- Instalar o PostgreSQL = sudo apt-get -y install postgresql

**Comandos Terminal**
- pg_createcluester -d /home/postgres/aula 11 aula --start= Local onde será criado o storage.
- pg_lsclusters = Mostrar clusters criados.
- su - postgres = Mudar para o usuários postgres
- psql = Verificar a instalação.

**pgAdmin Instalação**
- Crie um arquivo já configurado no repositório de arquivos = sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$ (lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
- Importar a chave do repositório oficial = sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
- Formas de instalar o pgAdmin 4:
  - Instale para os modos desktop e web = sudo apt install pgadmin4
  - Instalar apenas no modo desktop = sudo apt install pgadmin4-desktop
  - Instale apenas para o modo web = sudo apt install pgadmin4-web
  - Configure o servidor da web, se você instalou pgadmin4-web= sudo /usr/pgadmin4/bin/setup-web.sh

**Comandos Terminal**
- pgadmin4 = Acessar o programa.
 vai gerar uma url, basta acessar a mesma no seu navegador, normalmente a primeira senha e admin

**Instalação PostgreSQL e pgAdmin no CentOS / Red Hat** 
**PostgreSQL Instalação**
- Instale o repositório RPM = sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
- Desative o módulo PostgreSQL integrado = sudo dnf -qy module disable postgresql
- Instale o PostgreSQL = sudo dnf install -y postgresql13-server
- Inicialize o banco de dados e habilite o início automático em 3 passos.
- Passo 1 = sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
- Passo 2 = sudo systemctl enable postgresql-13
- Passo 3 = sudo systemctl start postgresql-13

**Comandos Terminal**
- systemctl status postgreslq-13 = Verificar se o banco de dados esta ativo

**pgAdmin Instalação**
- Para usar este repositório, primeiro desinstale quaisquer pacotes de repo pgAdmin que você possa já ter instalado, por exemplo; sudo rpm -e pgadmin4-fedora-repo ou sudo rpm -e pgadmin4-redhat-repo
- Fedora = sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-fedora-repo-1-1.noarch.rpm
- Redhat or CentOS = sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm
- Instalar para os modos desktop e web = sudo yum install pgadmin4
- Instale apenas no modo desktop = sudo yum install pgadmin4-desktop
- Instale apenas para o modo web = sudo yum install pgadmin4-web
- Configure o servidor da web, se você instalou pgadmin4-web= sudo /usr/pgadmin4/bin/setup-web.sh

**Instalação PostgreSQL e pgAdmin no Windows**
**PostgreSQL Instalação**
- Entre no site, faça o download da sua versão e instale.

**pgAdmin Instalação**
- Entre no site, faça o download da sua versão e instale.

Capitulo 2 - Objetos e tipos de dados do PostgreSQL
**Configurações (Objetivos da Aula)**
- O arquivo postgresql.conf
- O arquivo pg_hba.conf
- O arquivo pg_ident.conf
- Comandos administrativos

**Parte 1: O arquivo postgresql.conf**
**Definição:**
- Arquivo onde estão definidas e armazenadas todas as configurações do servidor PostgreSQL.
- Alguns parâmetros só podem ser alterados com uma reinicialização do banco de dados.
- A view pg_settings, acessada por dentro do banco de dados, guarda todas as configurações atuais.

**postgresql.conf**
- Ao acessar a view pg_setting, é possível visualiar todas as configurações atuais:
  - SELECT name, setting
  - FROM pg_setting;
- Ou é possível usar o comando:
  - SHOW [parâmetro];

**Localização do arquivo postgresql.conf**
- Por padrão, encontra-se dentro do diretório PGDATA definido no momento da inicialização do cluster de banco de dados.
- No sistema operacional Ubuntu, se o PostgreSQL foi inttalado a partir do repositório oficial, o local do arquivo postgresql.conf será diferente do diretório de dados.
  /etc/postgresql/[versão]/[nome do cluster]/postgresql.conf

**Configurações de conexão**
- LISTEN_ADDRESSES
  - Endereço(s) TCP/IP das interfaces que o servidor PostgreSQL vai escutar/liberar conexões.
- PORT
  - A porta TCP que o servidor PostgreSQL vai ouvir. O padrão é 5432
- MAX_CONNECTIONS
  - Número máximo de conexões simultâneas no servidor PostgreSQL.
- SUPERUSER_RESERVED_CONNECTIONS
  - Número de conexões (slots) reservadas para conexões ao bando de dados super usuários.

**Configurações de autenticação**
- AUTHENTICATION_TIMEOUT
  - Tempo máximo em segundos para o cliente conseguir uma conexão com o servidor.
- PASSWORD_ENCRYPTION
  - Algoritmo de criptografia das senhas dos novos usuários criados no banco de dados.
- SSL
  - Habilita a conexão criptografada por SSL ( Somente se o PostgreSQL foi compilado com suporte SSL)

**Configurações de memória**
- SHARED_BUFFERS
  - Tamanho da memória compartilhada do servidor PostgreSQL para cache/buffer de tabelas, índices e demais relações.
- WORK_MEM
  - Tamanho da memória para operações de agrupamento e ordenação (ORDER BY, DISTINCT, MERGE JOINS).
- MAINTENANCE_WORK_MEM
  - Tamanho da memória para operações como VACUUM, INDEX, ALTER TABLE.

**Parte 2: O Arquivo pg_hba.conf**
**Definição:**
- Arquivo responsável pelo controle de autenticação dos usuários no servidos PostgreSQL.
- O formato do arquivo pode ser:

**Métodos de autenticação:**
- TRUST = conexão sem requisição de senha.
- REJECT = rejeitar conexões.
- MD5 = criptografia md5.
- PASSWORD = senha sem criptografia.
- GSS = generic securty service application program interface.
- SSPI = security support provider interface - somente Windows.
- KRB5 = kerberos V5.
- IDENT = utiliza o usuário do sistema operacional do cliente via ident server.
- PEER = utiliza o usuário do sistema operacional do cliente.
- LDAP = Idap server.
- RADIUS = radius server.
- CERT = autenticação via certificado ssl do cliente.
- PAM = pluggable authentication modules. O usuário precisar estar no banco de dados.

**Parte 3: O arquivo pg_ident.conf**
**Definição:**
- Arquivo responsável por mapear os usuários do sistema operacional com os usuários do banco de dados.
- Localizado no diretório de dados PGDATA de sua instalação.
- A opção ident deve ser utilizada no arquivo pg_hba.conf

**Parte 4: Comandos administrativos**
**Comandos no Ubuntu**
- pg_lsclusters = Lista todos os clusters PostgreSQL.
- pg_createcluster <version> <cluster name> = Cria um novo cluster PostgreSQL.
- pg_dropcluster <version> <cluster> = Apaga um cluster PostgreSQL
- pg_ctlcluster <version> <cluster> <action> = Start, Stop, Status, Restart de clusters PostgreSQL.

**Comandos no CentOS**
- systemctl <action> <cluster>
  - systemctl start postgresql-11 = Inicia o cluster PostgreSQL
  - systemctl status postgresql-11 = Mostra o status do cluster PostgreSQL
  - systemctl stop postgresql-11 = Para o cluster PostgreSQL
  - systemctl restart postgresql-11 = Reinicia o cluster PostgreSQL

**Comandos no Windows**
- Abrir o arquivo services (serviços)
- Clicar com o botão direito sobre o postgresql = (terá todas as opções)

**Binários do PostgreSQL: (Comandos para PostgreSQL compilado)** 
- createdb
- createuser
- dropdb
- initdb
- pg_ctl
- pg_basebackup
- pg_dump / pg_dumpall
- pg_restore
- psql
- reindexdb
- vacuumdb

**Arquitetura / Hierarquia**
**Cluster**
- Coleção de bancos de dados que compartilham as mesmas configurações (arquivos de configuração) do PostgreSQL e do sistema operacional (porta, listen_addresses, etc).

**Banco de dados (database)**
- Conjunto de schemas com seus objetos/relações (tabelas, funções, views, etc).

**Schema**
- Conjunto de objetos/relações (tabelas, funções, views, etc).

**Ferramenta PGAdmin (Objetivos da Aula)**
- Visão geral do PGAdmin4.
- Configurar acesso ao servidor PostgreSQL.
- Visão geral do cluster e nosso primeiro comando.

**Importante para conexão:**
- Liberar acesso ao cluster em postgresql.conf
- Liberar acesso ao cluster para o usuário do banco de dados em pg_hba.conf
- Criar/editar usuários

**Comando para criar a senha do usuário master**
- ALTER USER postgres PASSWORD '123';

**Comando para dar reload de configuração**
- pg_ctlcluster 11 aula reload

**Usuários (Objetivos da Aula)**
- Conceitos user/roles/groups
- Administrando users/roles/groups
- Administrando acessos (GRANT)

**Parte 1: Conceitos users/roles/groups**
**Definição:**
- Roles (papéis ou funções), users (usuários) e grupo de usuários são "contas", perfis de atuação em um banco de dados, que possuem permissões em comum ou específicas.
- Nas versões anteriores do PostgreSQL 8.1, usuários e roles tinham comportamentos diferentes. Atualmente, roles e users são alias.
- É possível que roles pertençam a outras roles:

**Parte 2: Administrando users/roles/groups**
Comando para criar o usuário com regras e grupos
- CREATE ROLE name [ [ WITH ] option [ ... ] ]
Opções disponiveis
- SUPERUSER | NOSUPERUSER
- CREATEDB | NOCREATEDB
- CREATEROLE | NOCREATEROLE
- INHERIT | NOINHERIT
- LOGIN | NOLOGIN
- REPLICATION | NOREPLICATION
- BYPASSRLS | NOBYPASSRLS
- CONNECTION LIMIT connlimit
- [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
- VALID UNTIL 'timestamp'
- IN ROLE role_name [, ...]
- IN GROUP role_name [, ...]
- ROLE role_name [, ...]
- ADMIN role_name [, ...]
- USER role_name [, ...]
- SYSID uid

**Associação entre roles**
- Quando uma role assume as permissões de outra role.
- Necessário a opção INHERIT.
No momento de criação da role:
- IN ROLE (passa a pertencer a role informada).
- ROLE (a role informada passa a pertencer a nova role).
Ou após a criação da role:
- GRANT [role a ser concedida] TO [role a assumir as permissões]

**Desassociar membros entre roles**
- REVOKE [role que será revogada] FROM [role que terá suas permissões revogadas]
Exemplo: REVOKE professores FROM daniel;

**Alterando uma role**
- ALTER ROLE role_specification [ WHIT ] option [ ...]

**Excluindo uma role**
- DROP ROLE role_specification;

**Parte 3: Administrando acessos (GRANT)**
**Definição:**
São os privilégios de acesso ao objetos do banco de dados.

**Privilégios:**
- tabela, coluna, sequence, database, domain, foreign data wrapper, foreign server, function, language, large object, schema, tablespace, type

**Revogando todas as permissões (Simplificado)**
- REVOKE ALL ON ALL TABLES IN SCHEMA [schema] FROM [role];
- REVOKE ALL ON SCHEMA [schema] FROM [role];
- REVOKE ALL ON DATABASE [database] FROM [role;

**Comandos usados para treinar no pgAdmin4**
-- ESSES DOIS TRACINHOS COMENTAM O COMANDO!
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 PASSWORD '123' IN ROLE professores;
DROP ROlE daniel;
CREATE ROLE daniel LOGIN PASSWORD '123' ROLE professores;

DROP ROLE daniel;
CREATE TABLE teste (nome varchar);
GRANT ALL ON TABLE teste TO professores;
CREATE ROLE daniel LOGIN PASSWORD '123';
DROP ROLE daniel;
CREATE ROLE daniel INHERIT LOGIN PASSWORD '123';

REVOKE professores FROM daniel;

**Objetos e comandos do banco de dados (Objetivos da Aula)**
- Database/Schemas/Objetos
- Tabelas/Colunas/Tipos de dados
- DML e DDL

**Parte 1: Database, Schemas e Objetos**
**Database**
- É o banco de dados.
- Grupo de schemas e seus objetos como tabelas, types, views, funções, entre outros.
- Seus schemas e objetos não podem ser compartilhados entre si.
- Cada database é separado um do outro compartilhando apenas usuários/roles e configurações do cluster PostgreSQL.

**Schemas**
- É um grupo de objetos, como tabelas, types, views, funções, entre outros.
- É possível relacionar objetos entre diversos schemas.
- Por exemplo: schema public e schema curso podem ter tabelas com o mesmo nome (teste por exemplo) relacionando-se entre si.

**Objetos**
- São as tabelas, views, funções, types, sequences, entre outros, pertencentes ao schemas

**Parte 2: Tabelas, Colunas e Tipos de dados:**
**Definição**
- Conjuntos de dados dispostos em colunas e linhas referentes a um objetivo comum.
- As colunas são consideradas como "campos de tabela", como atributos da tabela.
- As linhas de uma tabela são chamadas também de tuplas, e é onde estão contidos os valores, os dados.

**Primary Key / Chave Primária / PK**  
- No conceito de modelo de dados relacional e obedecendo as regras de normalização, uma PK é um conjunto de um ou mais campos que nunca se repetem em uma tabela e que seus valores garantem a integridade do dado único e a utilização do mesmo como referência para o relacionamento entre demais tabela.
- não pode haver duas ocorrências de uma mesma entidade com o mesmo conteúdo na PK.
- A chave primária não pode ser composta por atributo opcional, ou seja, atributo que aceite nulo.
- Os atributos identificadores devem ser o conjunto mínimo que pode identificar cada instância de uma entidade.
- Não devem ser usadas chaves externas.
- Não deve conter informação volátil.

**Foreign Key / Chave Estrangeira / FK**
- Campo, ou conjunto de campos que são referências de chaves primárias de outras tabelas ou da mesma tabela.
- Sua principal função é garantir a integridade referencial entre tabelas.

**Parte 3: DML e DDL** 
**DML (Data Manipulation Language)**
- Linguagem de manipulação de dados
- INSERT, UPDATE, DELETE, SELECT
- O SELECT, alguns consideram como DML, outros como DQL, que significa data query language, ou linguagem de consulta de dados.

**DDL (Data Definition Language)**
- Linguagem de definição de dados.
- CREATE, ALTER, DROP

**CREATE / ALTER / DROP**
- CREATE [objeto] [nome do objeto] [opções]
- ALTER [objeto] [nome do objeto] [opções]
- DROP [objeto] [nome do objeto] [opções]

**INSERT**
- Modelo 1:
  INSERT INTO [nome da tabela] ([campos da tabela,])
  VALUES ([valores de acordo com a ordem dos campos acima,]);

- Modelo 2:
  INSERT INTO [nome da tabela] ([campos da tabela,])
  SELECT [valores de acordo com a ordem dos campos acima,]

**UPDATE**
UPDATE [nome da tabela] SET
[campo1] = [novo valor do campo1],
[campo2] = [novo valor do campo2],
...
[WHERE + condições]
- Atenção: Muito cuidado com os updates. Sempre utilize-os com condição.

**DELETE**
DELETE FROM [nome da tabela]
[WHERE + condições]
- Atenção: Muito cuidado com os deletes. Sempre utilize-os com condição.

**SELECT**
SELECT [campos da tabela]
FROM [nome da tabela]
[WHERE + condições]
- Dicas de Boas Práticas = Evite sempre que puder o SELECT*

**Comandos usados para treinar no pgAdmin4**
-- ESSES DOIS TRAÇINHOS COMENTAM O COMANDO!
-- 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)
);



Capitulo 3 - Fundamentos da Structured Query Language (SQL)
**DML (Objetivos da Aula)**
- Revisão
- DML / CRUD
- Truncate

**Revisão**
- PK, FK, Tipos de dados, DDL, DML
- Idempotência
  - Propriedade que algumas ações/operações possuem possibilitando-as de serem executadas diversas vezes sem alterar o resultado após a aplicação inicial.

**Idempotência**
- IF EXISTS
- Comandos pertinentes ao DDL e DML

**Melhores práticas em DDL**
Importante as tabelas possuírem campos que realmente serão utilizados e que sirvam de atributo direto a um objetivo em comum.
- Criar/Acrescentar colunas que são "atributos básicos" do objeto:
  Exemplo: tabela CLIENTE: coluna TELEFONE / coluna AGENCIA_BANCARIA
- Cuidado com regras (constraints)
- Cuidado com o excesso de FKs
- Cuidado com o tamanho indevido de colunas
  Exemplo: coluna CEP VARCHAR(255)

**Parte 2: DML - CRUD**
**SELECT**
- SELECT (campos,)
- FROM tabela
- [condições]

Exemplo:
SELECT numero, nome FROM banco;
SELECT numero, nome FROM banco WHERE ativo IS TRUE;
SELECT nome FROM cliente WHERE email LIKE '%gmail.com';

SELECT numero FROM agencia
WHERE banco_numero IN (SELECT numero FROM banco WHERE nome ILIKE '%Bradesco%');

**SELECT - Condição (WHERE / AND / OR)**
WHERE (coluna/condição):
- = , > / >= , < / <= , <> / != , LIKE , ILIKE , IN
- Primeira condição sempre WHERE.
- Demais condições, AND e OR.

**SELECT - Idempotência**
SELECT (campos,)
FROM tabela1
WHERE EXISTS (
SELECT (campo,)
FROM tabela2
WHERE campo1 = valor1
[AND/OR campoN = valorN]
);
Não é uma boa prática. Melhor prática utilizar LEFT JOIN.

**INSERT**
- INSERT (campos da tabela,) VALUES (valores,);
- INSERT (campos da tabela,) SELECT (valores,);

**INSERT - Idempotência**
INSERT INTO agencia (banco_numero,numero,nome) VALUES (341,1'Centro da cidade');

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');


**ON CONFLICT**
INSERT INTO agencia (banco_numero,numero,nome) VALUES (341,1,'Centro da cidade') ON CONFLICT (banco_numero,numero) DO NOTHING;

**UPDATE**
UPDATE (tabela) SET campo1 = novo_valor WHERE (condição);

**DELETE**
DELETE FROM (tabela) SET campo1 = novo_valor WHERE (condição);

**TRUNCATE**
**Definição**
"Esvazia" a tabela
- TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [ , ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

**Comandos usados na aula**
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;

**Funções Agregadas (Objetivos da Aula)**
- Funções agregadas

**Parte 1: Funções Agregadas**
- AVG, COUNT (opção HAVING), MAX, MIN, SUM
- https://www.postgresql.org/docs/11/functions-aggregate.html

**Comandos usados na aula**
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;

**Relacionamento entre tabelas (Objetivos da Aula)**
- JOINS

**Definição**
- JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN

**JOIN (INNER)**
SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
JOIN tabela_2
	ON tabela_2.campo = tabela_1.campo

**LEFT JOIN (OUTER)**
SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
LEFT JOIN tabela_2
	ON tabela_2.campo = tabela_1.campo

**RIGHT JOIN (OUTER)**
SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
RIGHT JOIN tabela_2
	ON tabela_2.campo = tabela_1.campo

**FULL JOIN (OUTER)**
SELECT tabela_1.campos, tabela_2.campos
FROM tabela_1
FULL JOIN tabela_2
	ON tabela_2.campo = tabela_1.campo

**CROSS JOIN**
Todos os dados de uma tabela serão cruzados com todos os dados da tabela referenciada no CROSS JOIN criando uma matriz.

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

**comandos usados na aula**
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

**CTE (Objetivos da Aula)**
- Common Table Expressions

**Parte 1: Common Table Expressions - CTE**
**Definição**
- Forma auxiliar de organizar "statements", ou seja, blocos de códigos, para consultas muito grandes, gerando tabelas temporárias e criando relacionamentos entre elas.
- Dentro dos statements podem ter SELECTs, INSERTs, UPDATEs ou DELETEs.

**WITH STATEMENTS**
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] ....

**comandos usados na aula**
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;


Capitulo 4 - Comandos avançados da Structured Query Language (SQL)
**Views (Objetivo da Aula)**
**Parte 1: O que são views**
- Views são visões.
- São "camadas" para as tabelas.
- São "alias" para uma ou mais queries.
- Aceitam comandos de SELECT, INSERT, UPDATE e DELETE.

**Idempotência**
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 (banco_numero, banco_nome, banco_ativo) AS (
	SELECT numero, nome, ativo
	FROM banco
);

SELECT banco_numero, banco_nome, banco_ativo
FROM vw_bancos;

**INSERT, UPDATE e DELETE**
CREATE OR REPLACE VIEW vw_bancos AS (
	SELECT numero, nome, ativo
	FROM banco
);

SELECT numero, nome, ativo
FROM vw_bancos;

- Funcionam apenas para VIEWs com apenas 1 tabela
INSERT INTO vw_bancos (numero, nome, ativo) VALUES (100, 'Banco CEM', TRUE);

UPDATE vw_bancos SET nome = 'Banco 100' WHERE numero = 100;

DELETE FROM vw_bancos WHERE numero = 100;

**TEMPORARY**
CREATE OR REPLACE TEMPORARY VIEW vw_bancos AS (
	SELECT numero, nome, ativo
	FROM banco
);
SELECT numero, nome, ativo
FROM vw_bancos;
- VIEW presente apenas na sessão do usuário
- Se você se desconectar e conectar novamente, a VIEW não estará disponível.

**RECURSIVE**
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)
);

- Obrigatório a existência dos campos da VIEW
- UNION ALL

Exemplos usados na aula:
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);

Exemplos usados na aula:
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

**WITH OPTIONS**
CREATE OR REPLACE VIEW vw_bancos AS (
	SELECT numero, nome, ativo
	FROM banco
);

INSERT INTO vw_bancos (numero, nome, ativo) VALUES (100, 'Banco CEM', FALSE)
-- OK

CREATE OR REPLACE VIEW vw_bancos_maiores_que_100 AS (
	SELECT numero, nome, ativo
	FROM vw_banco
	WHERE numero > 100
) WITH LOCAL CHECK OPTION;

INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (99, 'Banco DIO', FALSE)
-- ERRO
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
);

INSERT INTO vw_bancos (numero, nome, ativo) VALUES (100, 'Banco CEM', FALSE)
-- OK

CREATE OR REPLACE VIEW vw_bancos_maiores_que_100 AS (
	SELECT numero, nome, ativo
	FROM vw_banco
	WHERE numero > 100
) WITH CASCADED CHECK OPTION;

INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (99, 'Banco DIO', FALSE)
-- ERRO
INSERT INTO vw_bancos_maiores_100 (numero, nome, ativo) VALUES (200, 'Banco DIO', FALSE))
-- ERRO
)

**Comandos usados na Aula**
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

**Comandos usados na Aula**
CREATE TABLE IF NOT EXISTS funcionarios (
	id SERIAL,
	nome VARCHAR(50),
	gerente INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY (gerente) REFERENCES funcionarios (id)
);

INSERT INTO funcionarios (nome, gerente) VALUES ('Anselmo', 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);

SELECT id, nome, gerente FROM funcionarios 

SELECT id, nome, gerente FROM funcionarios WHERE gerente IS NULL;

SELECT id, nome, gerente FROM funcionarios WHERE gerente IS NULL
UNION ALL
SELECT id, nome, gerente FROM funcionarios WHERE id = 999; -- APENS PARA EXEMPLIFICAR

CREATE OR REPLACE RECURSIVE VIEW vw_func (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_func ON vw_func.id = funcionarios.gerente
);

SELECT id, gerente, funcionario
FROM vw_func;

**Transações (Objetivo da Aula)**
**Definição**
- Conceito fundamental de todos os sistemas de bancos de dados.
- Conceito de múltiplas etapas/códigos reunidos em apenas 1 transação, onde o resultado precisa ser tudo ou nada.

Exemplos:
UPDATE conta SET valor = valor - 100.00
	WHERE nome = 'Alice';

UPDATE conta SET valor = valor + 100.00
	WHERE nome = 'Bob';

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;

**Comandos usados na Aula**
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;

**Funções (Objetivo da Aula)**
**Definição**
- Conjunto de códigos que são executados dentro de uma transação com a finalidade de facilitar a programação e obter o reaproveitamento/reutilização de códigos.
- Existem 4 tipos de funções:
  - query language functions (funções escritas em SQL).
  - procedural language functions (funções escritas em, por exemplo PL/pqSQL ou PL/py).
  - Internal functions
  - C-language functions

Porém, o foco aqui é falar sobre USER DEFINED FUNCTIONS.
Funções que podem ser criadas pelo usuário.

**Linguagens**
- SQL, PL/PGSQL, PL/PY, PL/PHP, PL/RUBY, PL/JAVA, PL/LUA ...
- https://www.postgresql.org/docs/11/external-pl.html

**Idempotência**
- CREATE OR REPLACE FUNCTION [ nome da função ]
  - Mesmo nome
  - Mesmo tipo de retorno
  - Mesmo número de parâmetros/argumentos

**Returns**
- Tipo de retorno (data type)
  - INTEGER, CHAR / VARCHAR, BOOLEAN, ROW, TABLE, JSON

**Sergurança**
- SECURITY (INVOKER) ou (DEFINER)

**Comportamento**
- IMMUTABLE
  - Não pode alterar o banco de dados.
  - Funções que garantem o mesmo resultado para os mesmos argumentos/parâmetros da função. Evitar a utilização de selects, pois tabelas podem, sofrer alterações.
- STABLE
  - Não pode alterar o banco de dados
  - Funções que garantem o mesmo resultado para os mesmos argumentos/parâmetros da função. Trabalha melhor com tipos de current_timestamp e outros tipos variáveis. Podem conter selects.
- VOLATILLE
  - comportamento padrão. Aceita todos os cenários.

**Segurança e Boas Práticas**
- CALLED ON NULL INPUT
  - Padrão. Se qualquer um dos parâmetros/argumentos for NULL, a função será executada.
- RETURNS NULL ON NULL INPUT
  - Se qualquer um dos parâmetros/argumentos for NULL, a função retornará NULL
- SECURITY INVOKER
  - Padrão. A função é executada com as permissões de quem executa
- SECURITY DEFINER
  - A função é executada com as permissões de quem criou a função.

**Recursos**
- COST
  - Custo/row em unidades de CPU
- ROWS
  - Número estimado de linhas que será analisada pelo planner


0
48

Comentários (1)

0
Ana Guerra

Ana Guerra

15/05/2021 05:38

Olha só, eu juro que eu fico te devendo uma depois dessa! 🚀😊


Ótimo conteúdo, parabéns. Estou estudando SQL e isso aqui veio a calhar e muito.


Sucesso! Bons estudos.

Um ser humano buscando conhecimento e sucesso em sua vida profissional

Brasil