Baixe Exemplo de Consultas PL/pgSQL para PostgreSQL e outras Notas de estudo em PDF para Informática, somente na Docsity!
Funções para Redes Funções cidr e inet host(inet) - - host('192.168.1.5/24') - - 192.168.1. masklen(inet) - - masklen('192.168.1.5/24') - - 24 netmask(inet) - - netmask('192.168.1.5/24') - - 255.255.255. network(inet) - - network('192.168.1.5/24') - - 192.168.1.0/ Função macaddr trunt(macaddr) - - trunc(maraddr '12:34:34:56:78:90:ab') - - 12:34:56:00:00: Funções de Informação do Sistema current_database() current_schema() current_schemas(boolean) current_user() inet_client_addr() inet_client_port() inet_server_addr() inet_server_port() pg_postmaster_start_time() version() has_table_privilege(user, table, privilege) - dá privilégio ao user na tabela has_table_privilege(table, privilege) - dá privilégio ao usuário atual na tabela has_database_privilege(user, database, privilege) - dá privilégio ao user no banco has_function_privilege(user, function, privilege) - dá privilégio ao user na função has_language_privilege(user, language, privilege) - dá privilégio ao user na linguagem has_schema_privilege(user, schema, privilege) - dá privilégio ao user no esquema has_tablespace_privilege(user, tablespace, privilege) - dá privilégio ao user no tablespace current_setting(nome) - valor atual da configuração set_config(nome, novovalor, is_local) - seta parâmetro de retorna novo valor pg_start_backup(label text) pg_stop_backup() pg_column_size(qualquer) pg_tablespace_size(nome) pg_database_size(nome) pg_relation_size(nome) pg_total_relation_size(nome) pg_size_pretty(bigint) pg_ls_dir(diretorio) pg_read_file(arquivo text, offset bigint, tamanho bigint) pg_stat_file(arquivo text)
6 - Funções Definidas pelo Usuário e Triggers O PostgreSQL oferece quatro tipos de funções:
- Funções escritas em SQL
- Funções em linguagens de procedimento (PL/pgSQL, PL/Tcl, PL/php, PL/Java, etc)
- Funções internas (rount(), now(), max(), count(), etc).
- Funções na linguagem C CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ RETURNS rettype ] { LANGUAGE langname | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | AS ' definition ' | AS ' obj_file ', ' link_symbol ' } ... [ WITH ( attribute [, ...] ) ] Para reforçar a segurança é interessante usar o parâmetro SECURITY DEFINER, que especifica que a função será executada com os privilégios do usuário que a criou. SECURITY INVOKER indica que a função deve ser executada com os privilégios do usuário que a chamou (padrão). SECURITY DEFINER especifica que a função deve ser executada com os privilégios do usuário que a criou. Uma grande força do PostgreSQL é que ele permite a criação de funções pelo usuário em diversas linguagens: SQL, PlpgSQL, TCL, Perl, Phyton, Ruby. Para ter exemplos a disposição vamos instalar os do diretório "tutorial" dos fontes do PostgreSQL: Acessar /usr/local/src/postgresql-8.1.3/src/tutorial e executar: make install Feito isso teremos 5 arquivos .sql. O syscat.sql traz consultas sobre o catálogo de sistema, o que se chama de metadados (metadata). O basic.sql e o advanced.sql são consultas SQL. O complex.sql trata da criação de um tipo de dados pelo usuário e seu uso. O func.sql traz algumas funções em SQL e outras em C.
CREATE OR REPLACE FUNCTION novo_empregado() RETURNS empregados AS $$ SELECT ROW('Nenhum', 1000.0, 25, '(2,2)')::empregados; $$ LANGUAGE SQL; Chamar assim: SELECT novo_empregado(); ou SELECT * FROM novo_empregado(); Funções SQL como fontes de tabelas CREATE TEMP TABLE teste (testeid int, testesubid int, testename text); INSERT INTO teste VALUES (1, 1, 'João'); INSERT INTO teste VALUES (1, 2, 'José'); INSERT INTO teste VALUES (2, 1, 'Maria'); CREATE FUNCTION getteste(int) RETURNS teste AS $$ SELECT * FROM teste WHERE testeid = $1; $$ LANGUAGE SQL; SELECT *, upper(testename) FROM getteste(1) AS t1; Tabelas Temporárias - criar tabelas temporárias (TEMP), faz com que o servidor se encarregue de removê-la (o que faz logo que a conexão seja encerrada). CREATE TEMP TABLE nometabela (campo tipo); Funções SQL retornando conjunto CREATE FUNCTION getteste(int) RETURNS SETOF teste AS $$ SELECT * FROM teste WHERE testeid = $1; $$ LANGUAGE SQL; SELECT * FROM getteste(1) AS t1; Funções SQL polimórficas As funções SQL podem ser declaradas como recebendo e retornando os tipos polimórficos anyelement e anyarray. CREATE FUNCTION constroi_matriz(anyelement, anyelement) RETURNS anyarray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT constroi_matriz(1, 2) AS intarray, constroi_matriz('a'::text, 'b') AS textarray; CREATE FUNCTION eh_maior(anyelement, anyelement) RETURNS boolean AS $$ SELECT $1 > $2; $$ LANGUAGE SQL; SELECT eh_maior(1, 2); Mais detalhes no capítulo 31 do manual.
6.2 - Funções em PlpgSQL As funções em linguagens procedurais no PostgreSQL, como a PlpgSQL são correspondentes ao que se chama comumente de Stored Procedures. Por default o PostgreSQL só traz suporte às funções na linguagem SQL. Para dar suporte à funções em outras linguagens temos que efetuar procedimentos como a seguir. Para que o banco postgres tenha suporte à linguagem de procedimento PlPgSQL executamos na linha de comando como super usuário do PostgreSQL: createlang plpgsql –U nomeuser nomebanco A PlpgSQL é a linguagem de procedimentos armazenados mais utilizada no PostgreSQL, devido ser a mais madura e com mais recursos. CREATE FUNCTION func_escopo() RETURNS integer AS $$ DECLARE quantidade integer := 30; BEGIN RAISE NOTICE 'Aqui a quantidade é %', quantidade; -- A quantidade aqui é 30 quantidade := 50; -- -- Criar um sub-bloco -- DECLARE quantidade integer := 80; BEGIN RAISE NOTICE 'Aqui a quantidade é %', quantidade; -- A quantidade aqui é 80 END; RAISE NOTICE 'Aqui a quantidade é %', quantidade; -- A quantidade aqui é 50 RETURN quantidade; END; $$ LANGUAGE plpgsql; => SELECT func_escopo(); CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; index ALIAS FOR $2; BEGIN -- algum processamento neste ponto END; $$ LANGUAGE plpgsql; CREATE FUNCTION concatenar_campos_selecionados(in_t nome_da_tabela) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; $$ LANGUAGE plpgsql;
select data_ctl('U','1997-11-01','06:36'); select data_ctl('U','1997-11-01','06:36'); Mais Detalhes no capítulo 35 do manual oficial. Funções que Retornam Conjuntos de Registros (SETS) CREATE OR REPLACE FUNCTION codigo_empregado (codigo INTEGER) RETURNS SETOF INTEGER AS ' DECLARE registro RECORD; retval INTEGER; BEGIN FOR registro IN SELECT * FROM empregados WHERE salario >= $1 LOOP RETURN NEXT registro.departamento_cod; END LOOP; RETURN; END; ' language 'plpgsql'; select * from codigo_empregado (0); select count (*), g from codigo_empregado (5000) g group by g; Funções que retornam Registro Para criar funções em plpgsql que retornem um registro, antes precisamos criar uma variável composta do tipo ROWTYPE, descrevendo o registro (tupla) de saída da função. CREATE TABLE empregados( nome_emp text, salario int4, codigo int4 NOT NULL, departamento_cod int4, CONSTRAINT empregados_pkey PRIMARY KEY (codigo), CONSTRAINT empregados_departamento_cod_fkey FOREIGN KEY (departamento_cod) REFERENCES departamentos (codigo) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) CREATE TABLE departamentos (codigo INT primary key, nome varchar); CREATE TYPE dept_media AS (minsal INT, maxsal INT, medsal INT); create or replace function media_dept() returns dept_media as ' declare r dept_media%rowtype; dept record; bucket int8; counter int; begin bucket := 0; counter := 0; r.maxsal :=0; r.minsal :=0;
for dept in select sum(salario) as salario, d.codigo as departamento from empregados e, departamentos d where e.departamento_cod = d.codigo group by departamento loop counter := counter + 1; bucket := bucket + dept.salario; if r.maxsal <= dept.salario or r.maxsal = 0 then r.maxsal := dept.salario; end if; if r.minsal <= dept.salario or r.minsal = 0 then r.minsal := dept.salario; end if; end loop; r.medsal := bucket/counter; return r; end ' language 'plpgsql'; Funções que Retornam Conjunto de Registros (SETOF, Result Set) Também requerem a criação de uma variável (tipo definidopelo user) CREATE TYPE media_sal AS (deptcod int, minsal int, maxsal int, medsal int); CREATE OR REPLACE FUNCTION medsal() RETURNS SETOF media_sal AS ' DECLARE s media_sal%ROWTYPE; salrec RECORD; bucket int; counter int; BEGIN bucket :=0; counter :=0; s.maxsal :=0; s.minsal :=0; s.deptcod :=0; FOR salrec IN SELECT salario AS salario, d.codigo AS departamento FROM empregados e, departamentos d WHERE e.departamento_cod = d.codigo ORDER BY d.codigo LOOP IF s.deptcod = 0 THEN s.deptcod := salrec.departamento; s.minsal := salrec.salario; s.maxsal := salrec.salario; counter := counter + 1; bucket := bucket + salrec.salario; ELSE IF s.deptcod = salrec.departamento THEN IF s.maxsal <= salrec.salario THEN s.maxsal := salrec.salario; END IF; IF s.minsal >= salrec.salario THEN
O gatilho fica associado à tabela especificada e executa a função especificada nome_da_função quando determinados eventos ocorrerem. O gatilho pode ser especificado para disparar antes de tentar realizar a operação na linha (antes das restrições serem verificadas e o comando INSERT, UPDATE ou DELETE ser tentado), ou após a operação estar completa (após as restrições serem verificadas e o INSERT, UPDATE ou DELETE ter completado). evento Um entre INSERT, UPDATE ou DELETE; especifica o evento que dispara o gatilho. Vários eventos podem ser especificados utilizando OR. Exemplos: CREATE TABLE empregados( codigo int4 NOT NULL, nome varchar, salario int4, departamento_cod int4, ultima_data timestamp, ultimo_usuario varchar(50), CONSTRAINT empregados_pkey PRIMARY KEY (codigo) ) CREATE FUNCTION empregados_gatilho() RETURNS trigger AS $empregados_gatilho$ BEGIN -- Verificar se foi fornecido o nome e o salário do empregado IF NEW.nome IS NULL THEN RAISE EXCEPTION 'O nome do empregado não pode ser nulo'; END IF; IF NEW.salario IS NULL THEN RAISE EXCEPTION '% não pode ter um salário nulo', NEW.nome; END IF; -- Quem paga para trabalhar? IF NEW.salario < 0 THEN RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome; END IF; -- Registrar quem alterou a folha de pagamento e quando NEW.ultima_data := 'now'; NEW.ultimo_usuario := current_user; RETURN NEW; END; $empregados_gatilho$ LANGUAGE plpgsql; CREATE TRIGGER empregados_gatilho BEFORE INSERT OR UPDATE ON empregados FOR EACH ROW EXECUTE PROCEDURE empregados_gatilho(); INSERT INTO empregados (codigo,nome, salario) VALUES (5,'João',1000); INSERT INTO empregados (codigo,nome, salario) VALUES (6,'José',1500); INSERT INTO empregados (codigo,nome, salario) VALUES (7,'Maria',2500); SELECT * FROM empregados;
INSERT INTO empregados (codigo,nome, salario) VALUES (5,NULL,1000); NEW – Para INSERT e UPDATE OLD – Para DELETE CREATE TABLE empregados ( nome varchar NOT NULL, salario integer ); CREATE TABLE empregados_audit( operacao char(1) NOT NULL, usuario varchar NOT NULL, data timestamp NOT NULL, nome varchar NOT NULL, salario integer ); CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN -- -- Cria uma linha na tabela emp_audit para refletir a operação -- realizada na tabela emp. Utiliza a variável especial TG_OP -- para descobrir a operação sendo realizada. -- IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'E', user, now(), OLD.; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'A', user, now(), NEW.; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', user, now(), NEW.*; RETURN NEW; END IF; RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER END; $emp_audit$ language plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON empregados FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit(); INSERT INTO empregados (nome, salario) VALUES ('João',1000); INSERT INTO empregados (nome, salario) VALUES ('José',1500); INSERT INTO empregados (nome, salario) VALUES ('Maria',250); UPDATE empregados SET salario = 2500 WHERE nome = 'Maria'; DELETE FROM empregados WHERE nome = 'João'; SELECT * FROM empregados; SELECT * FROM empregados_audit;
SELECT * FROM empregados; SELECT * FROM empregados_audit; Crie a mesma função que insira o nome da empresa e o nome do cliente retornando o id de ambos create or replace function empresa_cliente_id(varchar,varchar) returns _int4 as ' declare nempresa alias for $1; ncliente alias for $2; empresaid integer; clienteid integer; begin insert into empresas(nome) values(nempresa); insert into clientes(fkempresa,nome) values (currval (''empresas_id_seq''), ncliente); empresaid := currval(''empresas_id_seq''); clienteid := currval(''clientes_id_seq''); return ''{''|| empresaid ||'',''|| clienteid ||''}''; end; ' language 'plpgsql'; Crie uma função onde passamos como parâmetro o id do cliente e seja retornado o seu nome create or replace function id_nome_cliente(integer) returns text as ' declare r record; begin select into r * from clientes where id = $1; if not found then raise exception ''Cliente não existente !''; end if; return r.nome; end; ' language 'plpgsql'; Crie uma função que retorne os nome de toda a tabela clientes concatenados em um só campo create or replace function clientes_nomes() returns text as ' declare x text; r record; begin x:=''Inicio''; for r in select * from clientes order by id loop x:= x||'' : ''||r.nome;
end loop; return x||'' : fim''; end; ' language 'plpgsql';
Criamos assim: CREATE ROLE nomeuser WITH ENCRYPTED PASSWORD '********'; Ao se logar: psql -U nomeuser nomebanco. CREATE ROLE nomeusuario VALID UNTIL 'data' Excluindo Usuário DROP USER nomeusuario; Como usuário, fora do banco: Criar Usuário CREATEROLE nomeusuario; Excluindo Usuário DROPUSER nomeusuario; Detalhe: sem espaços. Criando Superusuário CREATE ROLE nomeuser WITH SUPERUSER ENCRYPTED PASSWORD '******'; Alterar Conta de Usuário ALTER ROLE nomeuser ENCRYPTED PASSWORD '******' CREATEUSER ALTER ROLE nomeuser VALID UNTIL '12/05/2006'; ALTER ROLE fred VALID UNTIL ’infinity’; ALTER ROLE miriam CREATEROLE CREATEDB; Obs.: Lembrando que ALTER ROLE é uma extensão do PostgreSQL. Listando todos os usuários: SELECT usename FROM pg_user; A tabela pg_user é uma tabela de sistema (_pg) que guarda todos os usuários do PostgreSQL. Também podemos utilizar: \du no psql Criando Um Grupo CREATE GROUP nomedogrupo; Adicionar/Remover Usuários Em Grupos ALTER GROUP nomegrupo ADD USER user1, user2,user3 ; ALTER GROUP nomegrupo DROP USER user1, user2 ; Excluindo Grupo DROP GROUP nomegrupo; Obs.: isso remove o grupo mas não remove os usuários do mesmo. Listando todos os grupos: SELECT groname FROM pg_group;
Privilégios Dando Privilégios A Um Usuário GRANT UPDATE ON nometabela TO nomeusuario; Dando Privilégios A Um Grupo Inteiro GRANT SELECT ON nometabela TO nomegrupo; Removendo Todos os Privilégios de Todos os Users REVOKE ALL ON nometabela FROM PUBLIC Privilégios O superusuário tem direito a fazer o que bem entender em qualquer banco de dados do SGBD. O usuário que cria um objeto (banco, tabela, view, etc) é o dono do objeto. Para que outro usuário tenha acesso ao mesmo deve receber privilégios. Existem vários privilégios diferentes: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE e USAGE. Os privilégios aplicáveis a um determinado tipo de objeto variam de acordo com o tipo do objeto (tabela, função, etc.). O comando para conceder privilégios é o GRANT. O de remover é o REVOKE. GRANT UPDATE ON contas TO joel; Dá a joel o privilégio de executar consultas update no objeto contas. GRANT SELECT ON contas TO GROUP contabilidade; REVOKE ALL ON contas FROM PUBLIC; Os privilégios especiais do dono da tabela (ou seja, os direitos de DROP, GRANT, REVOKE, etc.) são sempre inerentes à condição de ser o dono, não podendo ser concedidos ou revogados. Porém, o dono do objeto pode decidir revogar seus próprios privilégios comuns como, por exemplo, tornar a tabela somente para leitura para o próprio, assim como para os outros. Normalmente, só o dono do objeto (ou um superusuário) pode conceder ou revogar privilégios para um objeto. -- Criação dos grupos CREATE GROUP adm; CREATE USER paulo ENCRYPTED PASSWORD 'paulo' CREATEDB CREATEUSER; -- Criação dos Usuários do Grupo adm CREATE USER andre ENCRYPTED PASSWORD 'andre' CREATEDB IN GROUP adm; CREATE USER michela ENCRYPTED PASSWORD 'michela' CREATEDB IN GROUP adm; O usuário de sistema (super usuário) deve ser um usuário criado exclusivamente para o PostgreSQL. Nunca devemos torná-lo dono de nenhum executável.
8 – Transações Uma transação acontece por completo (todas as operações) ou nada acontece. Também a transação deve garantir um nível de isolamento das demais transações, de maneira que as demais transações somente enxerguem as operações após a transação concluída. Caso haja um erro qualquer na transação ou falha no sistema o SGBR irá executar um comando ROLLBACK. Transações são uma forma de dar suporte às operações concorrentes, garantindo a segurança e integridade das informações. Garantir que duas solicitações diferentes não efetuarão uma mesma operação ao mesmo tempo. Ao consultar o banco de dados, uma transação enxerga um snapshot (instantâneo) dos dados, como estes eram no exato momento em que a consulta foi solicitada, desprezando as mudanças ocorridas depois disso. O PostgreSQL trata a execução de qualquer comando SQL como sendo executado dentro de uma transação. Na versão 8 apareceram os SAVEPOINTS (pontos de salvamento) , que guardam as informações até eles. Isso salva as operações existentes antes do SAVEPOINT e basta um ROLLBACK TO para continuar com as demais operações. O PostgreSQL mantém a consistência dos dados utilizando o modelo multiversão MVCC (Multiversion Concurrency Control), que permite que leitura não bloqueie escrita nem escrita bloqueie leitura. O PostgreSQL também conta com um nível de isolamento chamado serializable (serializável), que é mais rigoroso e emula execução serial das transações. BEGIN; UPDATE contas SET saldo = saldo – 100.00 WHERE codigo = 5; SAVEPOINT meu_ponto_de_salvamento; UPDATE contas SET saldo = saldo + 100.00 WHERE codigo = 5; -- ops ... o certo é na conta 6 ROLLBACK TO meu_ponto_de_salvamento; UPDATE contas SET saldo = saldo + 100.00 WHERE conta = 6; COMMIT; Exemplos: CREATE TABLE contas(codigo INT2 PRIMARY KEY, nome VARCHAR(40), saldo NUMERIC()); INSERT INTO contas values (5, 'Ribamar', 500.45); Uma transação é dita um processo atômico, o que significa que ou acontecem todas as suas operações ou então nenhuma será salva. Vamos iniciar a seguinte transação na tabela acima: BEGIN; -- Iniciar uma transação UPDATE contas SET saldo = 800.35 WHERE codigo= 5;
SELECT nome,saldo FROM contas WHERE codigo = 5; COMMIT; -- Executar todos os comandos da transação Agora para testar se de fato todas as operações foram salvas execute: SELECT nome,saldo FROM contas WHERE codigo = 5; Vamos a outro teste da atomicidade das transações. Intencionalmente vamos cometer um erro no SELECT (FRON): BEGIN; -- Iniciar uma transação UPDATE contas SET saldo = 50.85 WHERE codigo= 5; SELECT nome,saldo FRON contas WHERE codigo = 5; COMMIT; -- Executar todos os comandos da transação Isso causará um erro e o comando ROLLBACK será automaticamente executado, o que garante que nenhuma das operações será realizada. Então execute a consulta para testar se houve a atualização: SELECT nome,saldo FRON contas WHERE codigo = 5; Remover Campo (versões anteriores a 7.3 não contam com esse recurso): BEGIN; LOCK TABLE nometabela; INTO TABLE nomenovo FROM nometabela; DROP TABLE nometabela; ALTER TABLE nomenovo RENAME TO nometabela; COMMIT; Alterar Tipos de Dados (versões antigas): BEGIN; ALTER TABLE tabela ADD COLUMN novocampo novotipodados; UPDATE tabela SET novocampo = CAST (antigocampo novotipodados); ALTER TABLE tabela DROP COLUMN antigocampo; COMMIT; Transações que não se Concretizam BEGIN; -- Iniciar uma transação UPDATE contas SET saldo = 50.85 WHERE codigo= 5; SELECT nome,saldo FRON contas WHERE codigo = 5; ROLLBACK; -- Cancelando todos os comandos da transação BEGIN; CREATE TABLE teste (id integer, nome text); INSERT INTO teste VALUES (1, 'Teste1'); INSERT INTO teste VALUES (2, 'Teste2'); DELETE FROM teste; COMMIT; BEGIN;
