"Create a function (named fn_bo_varios_states) that receives as a parameter the code (INTEGER) of a victim and return (TRUE or FALSE) if she has already recorded occurrence bulletins in more than one territory state Brazilian. "
How can I make this comparison between the data in the same column?
Should I return TRUE or FALSE, would I have to print as a message? How do I do this?
Well from what I understand of your question, I've done something to try to help. But I did in PL / SQL using Oracle, in case you're using Postgresql sometimes you need to change something in the syntax, but just follow the logic. Good come on ...
I created a victim table, in it I have idBoletim, idVit, and state. I created it just 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 is the function that I created to verify if this id of the victim has bulletins 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 put it to print the message inside the function.
Remember that to print message on the screen you need to use this command:
set serveroutput on; in Oracle, I do not remember if you need it in 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;
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.