-- -----------------------------------------------------------------------------------------------------------------------------------
-- Escreva uma TF e um T para determinar o número sequencial de uma CP de uma tabela da base de dados. Este número sequencial deve ser
-- incrementado em 5 unidades a cada linha Inserida na Tabela, sempre sendo referenciado em relação ao último valor da CP da tabela.
-- Note:
   - O campo CP da tabela deverá ser do tipo Numérico.
   - Considere seu gatilho sendo executado mesmo em uma tabela sem tuplas.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Analisando: Tabela: idiomas, Momento: ANTES e o Evento: INSERT -> Isso DETERMINA o Nome da TF: idiomasbi() - Note é uma proposta.
-- O Nome de uma TF deve ser ÚNICO em uma Base de Dados.
-- Por isso é boa prática vincular o nome da TF ao nome da tabela, ao momento e ao evento.
-- 
-- PORTANTO, ANTES de INSERIR o registro em idiomas, deve-se:
-- Verificar qual é o máximo valor gravado da CP na tabela.
   -- usaremos o comando SQL, na forma:
      select MAX(cpidioma) as mreg from idiomas; -- Linha sem comentário! TESTE NO PGAdmin 4 (QueryTool).
-- Temos que registrar este em uma VARIÁVEL.
   -- Em PLPGSQL (Procedural Language from PostgreSQL) usamos
   -- o DECLARE com o nome e tipo de dado.
   -- Os tipos de dados de variáveis são os mesmos dos campos de tabelas.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Algortiomo da TF.
Declarar variavel: vmax integer;
InicioBloco
  vmax <- 'Ler' o máximo valor do campo cpidioma idiomas;
  SE ( vmax é NULO )
     ENTÃO
       NEW.cpidioma <- 5;
     SENÃO
       NEW.cpidioma <- vmax + 5;
  FimSe;
  Retorne a nova linha;
FimBloco;
-- traduzindo para plpgsql
declare
   vmax integer;
begin
   select MAX(cpidioma) into vmax from idiomas;
   if ( vmax is null )
   then
      NEW.cpidioma:= 5;
   else
      NEW.cpidioma:= vmax + 5;
   end if;
   return new;
end;
-- O segmento de código pode ser implementado como TRIGGER FUNCTION no PostgreSQL. 
-- DEPOIS de escrever uma Trigger Function temos que 'vincular' a TF na tabela alvo. Devemos escrever uma TRIGGER na tabela.
-- 
-- -----------------------------------------------------------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Seguimos com a implementação.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Função de Gatilho: idiomasbi()
-- FUNCTION: public.idiomasbi()
-- DROP FUNCTION IF EXISTS public.idiomasbi();

CREATE OR REPLACE FUNCTION public.idiomasbi()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
declare
   vmax integer;
begin
   select MAX(ididioma) into vmax from idiomas;
   if ( vmax is null )
   then
      NEW.ididioma:= 5;
   else
      NEW.ididioma:= vmax + 5;
   end if;
   return new;
end;
$BODY$;

ALTER FUNCTION public.idiomasbi() OWNER TO postgres;
COMMENT ON FUNCTION public.idiomasbi() IS 'determina o próximo valor da CP da tabela';
-- -----------------------------------------------------------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Gatilho da tabela que dispara o idiomasbi()
-- Trigger: biidiomas
-- DROP TRIGGER IF EXISTS biidiomas ON public.idiomas;
CREATE OR REPLACE TRIGGER biidiomas
    BEFORE INSERT
    ON public.idiomas
    FOR EACH ROW
    EXECUTE FUNCTION public.idiomasbi();
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Agora podemos TESTAR a eficácia da Função de Gatilho e do Gatilho executando 'INSERTs' em idiomas.
select * from idiomas;
insert into idiomas
       values (20,
               'Português - Brasil',
               'Idioma de origem latina com derivação em vários paises, inclusive o Brasil que desenvolveu diversos dialetos',
               '2024-04-18');
-- O comando insert acima 'força' o valor da CP (ididoma) para '20'; entretanto, a Função de Gatilho retorna o valor 5
-- para a ididioma pois a tabela estava sem tuplas (vazia).
-- Executando outros inserts
insert into idiomas values (NULL,
                            'inglês - americano',
                            'Idioma inglês com expressões usadas nos USA',
                            '2024-04-18'),
                           (null,
                            'inglês - americano',
                            'Idioma inglês com expressões usadas na Grã-Bretanha',
                            '2024-04-18'),
                           (null,
                            'espanhol - europeu',
                            'Idioma de origem latina com derivação em vários paises',
                            '2024-04-18');
-- 
-- Direcionando o comando de leitura de todas as linhas de idiomas para um arquivo
copy idiomas to '/tmp/idiomas.txt';
-- Tem-se os seguintes registros:
5	Português - Brasil	Idioma de origem latina com derivação em vários paises, inclusive o Brasil que desenvolveu diversos dialetos	2024-04-18
10	inglês - americano	Idioma inglês com expressões usadas nos USA	2024-04-18
15	inglês - americano	Idioma inglês com expressões usadas na Grã-Bretanha	2024-04-18
20	espanhol - europeu	Idioma de origem latina com derivação em vários paises	2024-04-18

-- melhorando a apresentação dos dados com
copy idiomas to '/tmp/idiomas.txt' using delimiters '|';
-- Tem-se, então:
5 |Português - Brasil|Idioma de origem latina com derivação em vários paises, inclusive o Brasil que desenvolveu diversos dialetos|2024-04-18
10|inglês - americano|Idioma inglês com expressões usadas nos USA|2024-04-18
15|inglês - americano|Idioma inglês com expressões usadas na Grã-Bretanha|2024-04-18
20|espanhol - europeu|Idioma de origem latina com derivação em vários paises|2024-04-18
--
-- Note os valores da ididioma que foram geradas pela Função de Gatilho.