upgrade-3.0b3.sql
2.16 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
CREATE OR REPLACE FUNCTION upgrade() RETURNS VOID AS $$
DECLARE
soft record;
nm integer;
atualiza record;
comp integer;
BEGIN
FOR soft IN select sw.nm_software, count(distinct prop.id_software) as n_repeticoes
from software sw
inner join proriedade_software prop on sw.id_software = prop.id_software
group by sw.nm_software
having count(distinct prop.id_software) > 1
order by count(distinct prop.id_software) desc LOOP
RAISE NOTICE 'O seguinte software possui entradas repetidas: %',soft.nm_software;
-- Escolhe um software para colocar em todos
SELECT DISTINCT id_software INTO nm
FROM software
WHERE nm_software = soft.nm_software
ORDER BY id_software asc
LIMIT 1;
-- Atualiza o valor de todas as entradas na tabela para o primeiro valor
FOR atualiza IN select distinct pr.id_propriedade_software
from software st
inner join proriedade_software pr on st.id_software = pr.id_software
where st.nm_software = soft.nm_software LOOP
RAISE NOTICE 'Atualizando o valor da propriedade = % com o software = %',atualiza.id_propriedade_software,nm;
BEGIN
UPDATE proriedade_software
SET id_software = nm
WHERE id_propriedade_software = atualiza.id_propriedade_software;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'ERRO!!!! Provavelmente a coleta estava repetida';
END;
END LOOP ;
END LOOP;
-- Finalmente limpa todos os softwares sem coleta
FOR soft IN select distinct sw.id_software
from software sw
left join proriedade_software prop on sw.id_software = prop.id_software
left join aquisicao_item aq on sw.id_software = aq.id_software
where prop.id_software is null
and aq.id_software is null LOOP
RAISE NOTICE 'Removendo software = %',soft.id_software;
BEGIN
DELETE FROM software
WHERE id_software = soft.id_software;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Erro na exclusão do software %',soft.id_software;
END ;
END LOOP ;
RETURN;
END;
$$ LANGUAGE 'plpgsql';