Professor:
Daniel Costa
Link para Download do Artigo:
Link para Material GitHub: (Professor)
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**
**Instalação pgAdmin**
Ferramenta gráfica para interagir com o banco de dados
**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
**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 ...
**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
Ana Guerra
15/05/2021 05:38