How can I compare the data in the same column?

Question

"Create a function (named fn_bo_varios_states) that takes as parameter the code (INTEGER) of a victim and return (TRUE or FALSE) if she has already registered reports of occurrence in more than one state of the territory brazilian."

How can I make this comparison between the data in the same column?

In case of returning TRUE or FALSE, I would have to print as message? How do I do this?

Answer 1

Well, from what I understand of your question, I’ve come up with something to try and help. However I did in PL/SQL using Oracle, in the case how you are using Postgresql sometimes you need to change something in the syntax, but just follow the logic. Well come on...

I created a table victim, I have idBoletim, idVitima and status. I created it only to test if it would work.

create table vitima(
    idBo integer primary key,
    idvitima integer not null,
    estado varchar(3) not null
)

I populated the table:

 insert into vitima values(12,1,'mg');
 insert into vitima values(13,1,'mg');
 insert into vitima values(14,1,'mg');
 insert into vitima values(16,1,'mg');

Now this is the function I created to check if such victim id has newsletters registered in different states.

create or replace function f_bo_varios_estados (p_idVitima in vitima.idvitima%type)
return boolean as 
        v_status boolean;
        v_cont int;
begin 
        select count(distinct estado) into v_cont from vitima where idvitima = p_idVitima;

        if (v_cont != 1) then
              v_status := true;
              return v_status;
        else 
              v_status := false;
              return v_status;
        end if;

end;

And finally, a block to test the function, I put the message in this block, but nothing prevents you to print the message within the function. Remember that to print a message on the screen you need to use this command: set serveroutput on; on the Oracle, I don’t remember if you need on the postgres either.

DECLARE
     status boolean;
BEGIN 
     status := f_bo_varios_estados(1);
     if(status = true)then
          dbms_output.put_line('Vitima registrou boletim em mais de 1 estado');
     else
          dbms_output.put_line('Vitima registrou boletim em 1 estado');
     end if;
END;

Ready! According to the way I populated the table there at the beginning, the output will be:

Vitima registrou boletim em 1 estado Procedimento PL/SQL concluído com sucesso.

Otherwise, output:

Vitima registrou boletim em mais de 1 estado Procedimento PL/SQL concluído com sucesso.