YBCmNJYj

1create or replace function aluno_e_da_turma(id_aluno int, id_turma int) returns boolean as $$
2begin
3	return exists (select * from aluno_turma a where a.id_aluno = $1 and a.id_turma = $2);		
4end;
5$$ language plpgsql;
6
7
8
9create or replace function boletim_do_aluno(id_aluno int, id_turma int, ano_letivo int) returns 
10table (descricao varchar(255),
11        prova_1 decimal(4,2), prova_2 decimal(4,2), prova_3 decimal(4,2), prova_4 decimal(4,2), media_1 decimal(4,2),
12        prova_5 decimal(4,2), prova_6 decimal(4,2), prova_7 decimal(4,2), prova_8 decimal(4,2), media_2 decimal(4,2), media_final decimal(4,2))
13AS $$
14declare
15	aula_iterator record;
16	boletim_disc record;
17begin
18	if not aluno_e_da_turma($1, $2) then
19		raise exception 'Aluno nao é matriculado na turma';
20	end if;
21
22	drop table if exists tmp_boletim_do_aluno;
23	CREATE TEMP TABLE tmp_boletim_do_aluno 
24	( id_tp_boletim_do_aluno serial, descricao varchar(255),
25	  prova_1 decimal(4,2), prova_2 decimal(4,2), prova_3 decimal(4,2), prova_4 decimal(4,2), media_1 decimal(4,2),
26	  prova_5 decimal(4,2), prova_6 decimal(4,2), prova_7 decimal(4,2), prova_8 decimal(4,2), media_2 decimal(4,2), media_final decimal(4,2)
27	);
28
29	for aula_iterator in (select * from aula a where a.id_turma = $2 and a.ano_letivo = $3) loop
30		select * into boletim_disc from boletim_da_disciplina($1, aula_iterator.id_aula);
31		insert into tmp_boletim_do_aluno values 
32			(default, boletim_disc.descricao, boletim_disc.prova_1, boletim_disc.prova_2, boletim_disc.prova_3,boletim_disc.prova_4, boletim_disc.media_1, 
33			boletim_disc.prova_5,  boletim_disc.prova_6, boletim_disc.prova_7, boletim_disc.prova_8, boletim_disc.media_2, boletim_disc.media_final);
34
35		raise notice '(%)', boletim_disc.prova_1;
36	end loop;
37
38	return query select 
39		t.descricao, cast(coalesce(t.prova_1, 0) as decimal(4,2)), 
40		cast(coalesce(t.prova_2, 0) as decimal(4,2)), cast(coalesce(t.prova_3, 0) as decimal(4,2)), 
41		cast(coalesce(t.prova_4, 0) as decimal(4,2)), cast(coalesce(t.media_1, 0) as decimal(4,2)), 
42		cast(coalesce(t.prova_5, 0) as decimal(4,2)),  cast(coalesce(t.prova_6, 0) as decimal(4,2)), 
43		cast(coalesce(t.prova_7, 0) as decimal(4,2)), cast(coalesce(t.prova_8, 0) as decimal(4,2)), 
44		cast(coalesce(t.media_2, 0) as decimal(4,2)), cast(coalesce(t.media_final, 0) as decimal(4,2))
45		from tmp_boletim_do_aluno t;
46end;
47$$ language plpgsql;