CREATE SCHEMA mac313_aula20;
SET SEARCH_PATH TO mac313_aula20;

------------------------------------------------

CREATE OR REPLACE FUNCTION olamundo() RETURNS INT AS
$$
    SELECT 1;
$$ LANGUAGE sql;
SELECT olamundo();  -- exemplo de chamada

CREATE OR REPLACE FUNCTION soma_numeros(IN nr1 INT, IN nr2 INT)
RETURNS INT AS
$$
    SELECT $1 + $2;
$$ LANGUAGE sql;
SELECT soma_numeros(300, 700) AS resposta;   -- exemplo de chamada

------------------------------------------------

CREATE TABLE Empregado (
     nome VARCHAR(20), salario NUMERIC, idade INTEGER);
 
INSERT INTO Empregado VALUES('João',2200,21);
INSERT INTO Empregado VALUES('José',4200,30);
 
CREATE FUNCTION ver_salario_dobrado(IN emp Empregado) 
RETURNS NUMERIC AS $$
        SELECT emp.salario * 2 AS salario;
$$ LANGUAGE SQL;
 
SELECT nome, ver_salario_dobrado(Empregado.*) AS salario_sonhado
FROM Empregado WHERE nome = 'João';
-- ou
SELECT nome, ver_salario_dobrado(ROW(nome, salario*1.1, idade)) 
  AS salario_sonhado FROM Empregado;

------------------------------------------------

CREATE FUNCTION novo_empregado() 
RETURNS Empregado AS $$
   SELECT VARCHAR(20) 'Fulano' AS nome, 1000.0 AS salario, 25 AS idade;
$$ LANGUAGE SQL;
-- ou
CREATE FUNCTION novo_empregado()
RETURNS Empregado AS $$
   SELECT ROW('Fulano', 1000.0, 25)::Empregado;
$$ LANGUAGE SQL;

-- Exemplos de chamadas:
SELECT novo_empregado();
-- ou
SELECT * FROM novo_empregado();

------------------------------------------------

CREATE TABLE Pessoa (chave1 INT, chave2 INT, nome VARCHAR(20));
INSERT INTO Pessoa VALUES (1, 1, 'João');
INSERT INTO Pessoa VALUES (1, 2, 'José');
INSERT INTO Pessoa VALUES (2, 1, 'Maria');
 
CREATE FUNCTION SelecionaPessoaPelaChave(INT) 
RETURNS Pessoa AS 
$$
   SELECT * FROM Pessoa WHERE chave1 = $1;
$$ LANGUAGE SQL;
SELECT * FROM SelecionaPessoaPelaChave(1) AS t;

------------------------------------------------

CREATE FUNCTION SelecionaPessoaPelaChave2(INT)
RETURNS SETOF Pessoa AS
$$
SELECT * FROM Pessoa WHERE chave1 = $1;
$$ LANGUAGE SQL;
SELECT * FROM SelecionaPessoaPelaChave2(1) AS t;

------------------------------------------------


CREATE OR REPLACE 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 func(VARCHAR, INTEGER) RETURNS INTEGER AS $$
DECLARE
  param1 ALIAS FOR $1;
  param2 ALIAS FOR $2; 
BEGIN
  RETURN length(param1) + param2; 
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func(param1 VARCHAR, param2 INTEGER) RETURNS INTEGER AS $$
BEGIN
  RETURN length(param1) + param2; 
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION  func(VARCHAR, INTEGER);
CREATE OR REPLACE FUNCTION func(VARCHAR, INTEGER) RETURNS INTEGER AS $$
BEGIN
  RETURN length($1) + $2; 
END;
$$ LANGUAGE plpgsql;

select func('oi',5);
------------------------------------------------

CREATE OR REPLACE FUNCTION concat_atrib_selecionados(tupla Empregado)
                                             RETURNS text AS $$
BEGIN
   RETURN tupla.nome || '-' || tupla.salario || '-' || tupla.idade;
END;
$$ LANGUAGE plpgsql;

SELECT concat_atrib_selecionados(Empregado.*) FROM Empregado;

------------------------------------------------

CREATE OR REPLACE FUNCTION obtemNomeEmpregados(NUMERIC)
RETURNS SETOF VARCHAR(20) AS $$
DECLARE
   registro RECORD;
   salario_interesse ALIAS FOR $1;
BEGIN
    FOR registro IN SELECT * FROM Empregado 
                    WHERE salario >= salario_interesse LOOP
            RETURN NEXT registro.nome;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- obtém o nome de todos com salário >= 2200
SELECT * FROM obtemNomeEmpregados(2200); 
------------------------------------------------
CREATE TYPE salario_por_idade AS (idade INT, salario NUMERIC);

CREATE FUNCTION obtemSalariosPorIdade() RETURNS SETOF salario_por_idade AS $$
DECLARE
   registro RECORD;
BEGIN
   FOR registro IN SELECT idade, AVG(salario) FROM Empregado GROUP BY idade LOOP
                RETURN NEXT registro;
        END LOOP;
        RETURN;
END
$$ LANGUAGE 'plpgsql';


SELECT obtemSalariosPorIdade();

CREATE FUNCTION obtemSalariosPorIdade2() RETURNS SETOF salario_por_idade AS $$
BEGIN
   RETURN QUERY 
       SELECT idade, AVG(salario) FROM Empregado GROUP BY idade ;
END
$$ LANGUAGE 'plpgsql';


SELECT obtemSalariosPorIdade2();

------------------------------------------------

CREATE OR REPLACE FUNCTION ContaAteDez1() RETURNS SETOF INT AS $$ 
DECLARE
   contador INT := 0;
BEGIN 
    LOOP
        IF contador < 10 THEN
            contador := contador + 1;
            RETURN NEXT contador;
	ELSE
	    EXIT; -- sai do laco
	END IF;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT ContaAteDez1();

CREATE OR REPLACE FUNCTION ContaAteDez2() RETURNS SETOF INT AS $$ 
DECLARE
   contador INT := 0;
BEGIN 
    LOOP
         contador := contador + 1;
         RETURN NEXT contador;

	 EXIT WHEN contador = 10; -- sai do laco
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT ContaAteDez2();

CREATE OR REPLACE FUNCTION ContaAteDez3() 
RETURNS SETOF INT AS $$ 
DECLARE
   contador INT := 0;
BEGIN 
    WHILE contador < 10 LOOP
         contador := contador + 1;
         RETURN NEXT contador;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT ContaAteDez3();

------------------------------------------------

CREATE TABLE Departamento(cod INT PRIMARY KEY, nome VARCHAR(20)); 
CREATE TABLE Funcionario(nome VARCHAR(30) PRIMARY KEY, cod_dept INT REFERENCES Departamento(cod));

CREATE OR REPLACE FUNCTION insereFunc(nome_func VARCHAR(30), cod_dept INT) 
RETURNS VOID AS $$
BEGIN
    BEGIN   -- Comeca bloco de tratamento de excecoes
       INSERT INTO Funcionario VALUES (nome_func, cod_dept);
           
       EXCEPTION WHEN SQLSTATE '23503' THEN
       BEGIN
           RAISE NOTICE 'O departamento informado para o func. nao existe!';
           RETURN;
       END;
    END;
    RAISE NOTICE 'O func. foi inserido com sucesso!';
END;
$$
LANGUAGE plpgsql;

SELECT insereFunc('John', 1);  -- vai gerar erro!
INSERT INTO Departamento values (1, 'Recursos Humanos') ;
SELECT insereFunc('John', 1);  -- agora vai dar certo!