0
I have the following table in the schema utfpr:
create table Matricula(
dis_sigla varchar(50),
tur_numero varchar(15),
alu_ra varchar(100) ,
mat_ano date,
mat_nota float not null,
constraint pk_matricula primary key (dis_sigla,tur_numero,alu_ra,mat_ano),
constraint fk_mat_dis_sigla_tur_numero foreign key (dis_sigla, tur_numero) references Turma(dis_sigla,tur_numero) on delete cascade,
constraint fk_mat_alu_nome foreign key (alu_ra) references Aluno(alu_ra) on delete cascade
);
I need to create a Function that receives as parameter the column of RA (alu_ra) and it returns me the Ras followed by the acronym of the discipline (dis_acronym), so I made this Function:
create function raSigla(ra utfpr.matricula.alu_ra%type) returns text as $$
declare
string utfpr.matricula.dis_sigla%type;
begin
select * into string from utfpr.matricula where matricula.alu_ra = ra;
return ra || ' -- ' || string;
end;
$$ language plpgsql;
select raSigla(alu_ra) from utfpr.matricula;
However, I am receiving the Ras with the repeated acronym, as follows, a fact that should not happen
1 1997530 -- EC35A
2 1997530 -- EC35A
3 1997530 -- EC35A
4 1997530 -- EC35A
5 1997530 -- EC35A
6 1997530 -- EC35A
7 1997530 -- EC35A
8 1997530 -- EC35A
How can I fix this?
1. why don’t you use a
distinct
? 2. from what I understand in the relationship you can take this information from the tableAluno
, can make the query in it (where the registration is key - having only one return)– rLinhares
Have you ever tried using a Return query or Return next and not a Return scale? https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
– anonimo