Técnicas Avançadas de Banco de Dados

Introdução

Este resumo aborda técnicas de banco de dados no PostgreSQL, com foco em Stored Procedures, Functions e Data Control Language (DCL).

Diferenças entre Procedures e Functions

Característica	Function	Procedure
Retorno	Sempre retorna um valor	Pode retornar ou não
Chamada	Usada em SELECT, WHERE, etc.	Executada com CALL
Uso comum	Cálculos, transformações	Ações como INSERT, DELETE

Tipos de Funções no PostgreSQL

SQL Functions

Apenas comandos SQL básicos

PL/pgSQL Functions

Suportam lógica com IF, LOOP, variáveis

Linguagens Externas

Usam C, Python, Java (maior desempenho, exigem configuração)

Vantagens de Procedures e Functions

Reutilização de código

Menor tráfego de rede

Melhor desempenho

Segurança

Centralização da lógica

Desvantagem: Dependência da plataforma/SGBD.

Exemplos

Function: Olá Pessoal

                    
                        CREATE FUNCTION sp_ola() RETURNS TEXT LANGUAGE SQL AS $$

                        SELECT 'Olá, Pessoal!';

                        $$;

Procedure: Olá Pessoal

                    
                        CREATE PROCEDURE nome() LANGUAGE plpgsql AS $$

                        BEGIN

                            RAISE NOTICE 'Olá, Pessoal!';

                        END;

                        $$;

Função com retorno:

                    
                        CREATE FUNCTION total_clientes() RETURNS BIGINT LANGUAGE SQL AS $$

                        SELECT COUNT(*) FROM cliente;

                        $$;

DCL – Data Control Language

Gerencia usuários, permissões e papéis no PostgreSQL.

Comandos:

                    
                        CREATE ROLE nome_papel;

                        ALTER ROLE nome_papel WITH ENCRYPTED PASSWORD '123';

                        DROP ROLE nome_papel;

                        GRANT UPDATE ON tabela TO papel;

                        REVOKE ALL ON tabela FROM PUBLIC;

Opções de CREATE ROLE:

LOGIN, SUPERUSER, CREATEDB, CREATEROLE
PASSWORD 'senha', CONNECTION LIMIT, VALID UNTIL
IN ROLE, NOINHERIT, etc.

Grupos e Permissões

Grupos são papéis sem LOGIN

Adicionar usuários a um grupo:


                        GRANT nome_grupo TO user1, user2;

Remover:


                        REVOKE nome_grupo FROM user2;