Return of a wrong create Function

Asked

Viewed 38 times

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 table Aluno, can make the query in it (where the registration is key - having only one return)

  • 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

No answers

Browser other questions tagged

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