-
Notifications
You must be signed in to change notification settings - Fork 0
/
funciones.sql
224 lines (193 loc) · 7.2 KB
/
funciones.sql
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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
------------------- PARTE B ----------------------------------
--/
DROP trigger IF EXISTS contador_actor ON actua;
--/
CREATE OR REPLACE FUNCTION incrementa() RETURNS trigger AS $$
DECLARE
cantAnterior int;
BEGIN
SELECT cantidad_films
into cantAnterior
FROM actor
WHERE NEW.id_actor = actor.id_actor;
if cantAnterior is null then
cantAnterior=0;
END IF;
UPDATE actor
SET cantidad_films = cantAnterior + 1
WHERE NEW.id_actor = actor.id_actor ;
RETURN NEW;
END;$$
LANGUAGE plpgsql;
CREATE TRIGGER contador_actor
AFTER INSERT OR UPDATE ON actua
FOR EACH ROW
EXECUTE PROCEDURE incrementa();
---------------------- PARTE C ----------------------------------
--/
-- PELICULA
CREATE OR REPLACE FUNCTION inserta_datos_pelicula()
RETURNS VOID AS $$
BEGIN
INSERT INTO pelicula
SELECT id_film, title, title_es, title_en, title_orig, year_,
plot, synopsis_es, synopsis_en, tagline, duration, color, id_youtube, url_ticket
FROM film
where id_film NOT IN( SELECT DISTINCT id_film from pelicula);
END;
$$ LANGUAGE PLPGSQL;
--DIRIGE
--/
CREATE or replace FUNCTION inserta_datos_dirige()
returns VOID AS $$
DECLARE
s film%rowtype;
r record; -- Variables auxiliares para iterar
t record;
j record;
dir integer;
casting TEXT[];
directores integer[];
id_direc integer;
paises TEXT[];
id_actor_mayor integer;
BEGIN
-- DIRIGE
FOR s IN (SELECT * FROM film)
LOOP -- Por cada pelicula con sus id_directores
directores = ARRAY[s.id_director1,s.id_director2,s.id_director3,s.id_director4,s.id_director5,s.id_director6,s.id_director7,s.id_director8,s.id_director9,s.id_director10,s.id_director11,s.id_director12,s.id_director13,s.id_director14];
FOREACH dir IN ARRAY directores -- Foreach es a partir de postgresql 9.1
LOOP
if(dir != 0)THEN
if((select id_director from director where dir = id_director) is not null and (select id_director from dirige where id_director = dir and id_film = s.id_film ) is null)THEN -- Se inserta unicamente si ya existia el director
INSERT INTO dirige
VALUES (dir, s.id_film);
END IF;
END IF;
END LOOP;
END LOOP;
END
$$ LANGUAGE plpgsql;
--/
CREATE or replace FUNCTION inserta_datos_pertenece()
returns VOID AS $$
DECLARE
s film%rowtype;
r record; -- Variables auxiliares para iterar
t record;
j record;
pai integer;
paises integer[];
BEGIN
FOR s IN (SELECT * FROM film)
LOOP -- Por cada pelicula con sus id_country's almacenamos sus paises y los procesamos
paises = ARRAY[s.id_country1,s.id_country2,s.id_country3,s.id_country4,s.id_country5,s.id_country6,s.id_country7,s.id_country8];
FOREACH pai IN ARRAY paises -- Foreach es a partir de postgresql 9.1
LOOP
if(pai != 0)THEN -- Si tal pais existe lo agregamos
if((select id_country from pais where pai = id_country) is not null and (select id_country from pertenece where id_country = pai and id_film = s.id_film ) is null)THEN -- Se inserta unicamente si ya existia el country
INSERT INTO pertenece
VALUES ( s.id_film,pai);
END IF;
END IF;
END LOOP;
END LOOP;
END
$$ LANGUAGE plpgsql;
-- Actor y actua
--/
CREATE or replace FUNCTION inserta_datos_actores()
returns VOID AS $$
DECLARE
s film%rowtype;
casting text[];
act text;
id_ult integer;
id integer;
BEGIN
id_ult = (SELECT max(id_actor) from ACTOR) + 1;
if(id_ult is null)then
id_ult = 0;
end if;
FOR s IN (SELECT * from film) -- Por cada pelicula con su cast
LOOP
casting = regexp_split_to_array(s.cast_ , ',');
FOREACH act in ARRAY casting
LOOP
if(act != '' and length(act) > 0)then
-- ACTOR
id = (SELECT id_actor FROM actor WHERE lower(trim(nombre)) = lower(trim(act)));
if(id is null) then
INSERT INTO ACTOR
VALUES(id_ult, lower(trim(act)), 0); -- Inserto como nuevo actor si no existia
id = id_ult;
id_ult = id_ult + 1;
end if;
-- ACTUA
if((select id_film from actua where id_film = s.id_film and id_actor = id) is null)then
INSERT INTO ACTUA
VALUES(id, s.id_film);
end if;
end if;
END LOOP;
casting = ARRAY[''];
END LOOP;
END
$$ LANGUAGE plpgsql;
--/
CREATE OR REPLACE FUNCTION insertar_datos()
RETURNS VOID AS $$
BEGIN
PERFORM inserta_datos_pelicula(),inserta_datos_dirige(),inserta_datos_pertenece(),inserta_datos_actores();
END;
$$ LANGUAGE plpgsql;
--/
--/
CREATE OR REPLACE FUNCTION check_datos_film()
RETURNS trigger AS $$
BEGIN
IF (EXISTS(SELECT id_film FROM film WHERE id_film = NEW.id_film)) THEN
raise notice 'Id repetido: %', new.id_film;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--/
CREATE OR REPLACE FUNCTION check_datos_director()
RETURNS trigger AS $$
BEGIN
IF (EXISTS(SELECT id_director FROM director WHERE id_director = NEW.id_director)) THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--/
CREATE OR REPLACE FUNCTION check_datos_pais()
RETURNS trigger AS $$
BEGIN
IF (EXISTS(SELECT id_country FROM pais WHERE id_country = NEW.id_country)) THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--/
DROP trigger IF EXISTS Before_Insert_film ON film;
--/
DROP trigger IF EXISTS Before_Insert_pais ON pais;
--/
DROP trigger IF EXISTS Before_Insert_directores ON director;
CREATE TRIGGER Before_Insert_film
BEFORE INSERT ON film
FOR EACH ROW
EXECUTE PROCEDURE check_datos_film();
CREATE TRIGGER Before_Insert_pais
BEFORE INSERT ON pais
FOR EACH ROW
EXECUTE PROCEDURE check_datos_pais();
CREATE TRIGGER Before_Insert_directores
BEFORE INSERT ON director
FOR EACH ROW
EXECUTE PROCEDURE check_datos_director();