Conditional Postgresql

Asked

Viewed 58 times

0

I’m new to the database and I could use a little help creating Rigger. I’m making a system where the user can open calls, I created a Rigger and need it to fire only when the type of call is occurrence (in the system, the types of calls are: occurrence, complaint, information, hazing, denunciation, praise, deception and interrupted connection).

The occurrence ID is 1.

CREATE OR REPLACE FUNCTION public.trigger_fct_tr_atend_tornozeleira()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
latitudeD float8;
longitudeD float8;

BEGIN
  BEGIN  
    BEGIN
    --descricao do estado para inserir na tabela de endereço
    SELECT latitude, longitude INTO STRICT latitudeD, longitudeD
    FROM endereco WHERE ID = NEW.id_endereco;
    EXCEPTION
      WHEN no_data_found THEN
      latitudeD = null, longitudeD = NULL;
    END;

    INSERT INTO atend_sac24(
                        id_atendimento,
                        data_criacao,
                        latitude,
                        longitude)
                VALUES (
                        NEW.id,
                        NEW.data_criacao,
                        latitudeD,
                        longitudeD);    

  END;
RETURN NEW;
END
$function$
;
  • And which field represents the occurrence?

  • Hi, sorry for the delay. I didn’t quite understand your question.

1 answer

0


Oops my dear, all right, follow a pattern of what you should do.

 -- drop table if exists chamados cascade;
 -- drop table if exists logs cascade;


create table if not exists chamados (
    id SERIAL PRIMARY KEY,
    name varchar(50) not null,
    kind integer not null
  );

create table if not exists logs (
      id SERIAL PRIMARY KEY,
      text varchar(50) not null,
      data timestamp not null
   ); 

CREATE OR REPLACE FUNCTION chamados_log_func()
RETURNS trigger AS
$BODY$
BEGIN
    -- neste momento eu verifico o tipo do chamado inserido
    -- caso seja do tipo 1 e somente se for verdadeiro é feito
    -- na tabela de logs. aqui sua lógica entra.
    if(new.kind = 1 ) then
        INSERT INTO logs(text, data) values('um chamado',NOW()::timestamp);
    end if;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_todos_os_chamados
AFTER INSERT ON chamados
FOR EACH ROW
EXECUTE PROCEDURE chamados_log_func();

insert into chamados(name,kind) values ('teste',1);

select * from logs;

It is created two tables, one of calls and one of logs, I create a function and define it as Trigger to observe the changes of Insert in calls, if it is type 1, I log and insert inside logs, now do it your way following this idea. The modifications go beyond, you can take the Id of the called as well as the time, finally customize in your way.

  • I get it. Thank you very much, I’ll try to do it here. But could you enlighten me where I would make the if conditional within my code so that it could only be done Insert and update if id equals 1?

  • This done in the line subsequent to my Begin, see that I make one and check if the kind is type '1', if I do Insert in my table logs, ie, you must make an if the same way and if id of the call is 1, do the Insert in atend_sac24. Check the answer now.

  • 1

    I did it. Thank you very much for your willingness!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.