Este es el Post #9 del Topic: Guía Práctica de PostgreSQL
El diagrama siguiente es la consecuencia de la creación de la base de datos con el script descrito en PostgreSQL: Sentencias DDL (1), Es un modelo pequeño, pero servirá para las futuras demostraciones con postgres.
Descripción: Una universidad realiza el registro de contactos, que son las personas que podrían ser potenciales postulantes a diferentes carreras. Los postulantes deberán pertenecer a un periodo académico y deberán elegir una modalidad asì como la carrera a la que quieren postular. Por lo general en un año solo existen dos periodos académicos, por ejemplo los periodos académicos del año 2008 fueron: 2008-1 y 2008-2. El contacto debe tener los datos personales del sujeto asì como la fecha de creación. El postulante debe tener registrado si asistió a su exámen de admisión, y si ingresó o no, debe tener también establecido el puntaje que alcanzó en el exámen.
Con esos datos podemos comenzar a elaborar nuestras primeras consultas usando instrucciones DML.
Antes de ir al grano, aclaro que
acostumbro crear funciones cuando se trata de retornar datos, como por
ejemplo una consulta que utilice la cláusula SELECT, y procedimientos
almacenados únicamente cuando se trata de realizar una operación que no
retorne ningún dato, por ejemplo, INSERT, UPDATE o DELETE.
En SQLServer se suele utilizar un Procedimiento Almacenado para cualquiera de los 2 casos descritos, aunque en PostGres también se puede retornar datos con procedimientos almacenados, sería ideal respetar la funcionalidad de cada uno. Las funciones desarrolladas en éste post se realizará haciedno uso del lenguaje procedural ‘plpgsql’, que generalmente trae PostGreSQL.
Si aún no tienes preparado la base de datos, aqui les dejo un script. Es la unión de las DDL y DML creadas en posts anteriores. Ésta creará el entorno en el que se realizarán las demostraciones.
Descargar Script: DBAdmision.sql
FUNCIONES:
1) Cree una función que devuelva los siguientes datos: <ver>
| Periodo | 101 | 309 | 310 | Total |
| 2005-1 | 1 | 7 | 7 | 15 |
| 2005-2 | 0 | 13 | 12 | 25 |
| 2006-1 | 0 | 17 | 18 | 35 |
| 2006-2 | 0 | 23 | 22 | 45 |
| 2007-1 | 0 | 27 | 28 | 55 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| CREATE OR REPLACE FUNCTION f_ejemplo_1()RETURNS SETOF "record" AS$BODY$DECLARE r RECORD;BEGIN FOR r IN SELECT idperacad AS "Periodo" ,SUM(CASE WHEN IDCarrera = '101' THEN 1 ELSE 0 END ) AS "101" ,SUM(CASE WHEN IDCarrera = '309' THEN 1 ELSE 0 END ) AS "309" ,SUM(CASE WHEN IDCarrera = '310' THEN 1 ELSE 0 END ) AS "310" ,COUNT(*) AS "TOTAL" FROM Persona.Postulante GROUP BY IDPerAcad LOOP RETURN NEXT r; END LOOP;RETURN;END;$BODY$LANGUAGE 'plpgsql' VOLATILE;--SELECT * FROM f_ejemplo_1() AS ("Periodo" CHARACTER, "101" bigint,"309" bigint,"310" bigint,"TOTAL" bigint); |
| Periodo | 101 | 309 | 310 | Total |
| 2005-1 | 1 | 7 | 7 | 15 |
| 2005-2 | 0 | 13 | 12 | 25 |
| 2006-1 | 0 | 17 | 18 | 35 |
| 2006-2 | 0 | 23 | 22 | 45 |
| 2007-1 | 0 | 27 | 28 | 55 |
| TOTAL | 1 | 87 | 87 | 175 |
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
| CREATE OR REPLACE FUNCTION f_ejemplo_2()RETURNS SETOF "record" AS$BODY$DECLARE r RECORD;BEGIN FOR r IN SELECT idperacad AS "Periodo" ,SUM(CASE WHEN IDCarrera = '101' THEN 1 ELSE 0 END ) AS "101" ,SUM(CASE WHEN IDCarrera = '309' THEN 1 ELSE 0 END ) AS "309" ,SUM(CASE WHEN IDCarrera = '310' THEN 1 ELSE 0 END ) AS "310" ,COUNT(*) AS "TOTAL" FROM Persona.Postulante GROUP BY IDPerAcad UNION SELECT 'TOTAL' AS "Periodo" ,SUM(CASE WHEN IDCarrera = '101' THEN 1 ELSE 0 END ) AS "101" ,SUM(CASE WHEN IDCarrera = '309' THEN 1 ELSE 0 END ) AS "309" ,SUM(CASE WHEN IDCarrera = '310' THEN 1 ELSE 0 END ) AS "310" ,COUNT(*) AS total FROM Persona.Postulante LOOP RETURN NEXT r; END LOOP;RETURN;END;$BODY$LANGUAGE 'plpgsql' VOLATILE;--SELECT * FROM f_ejemplo_2() AS ("Periodo" CHARACTER, "101" bigint,"309" bigint,"310" bigint,"TOTAL" bigint); |
3) Cree una función que liste todos los postulantes de un determinado periodo académico. La lista debe reiniciar la numeración por orden alfabético. <ver>
| Nº | Apellidos y Nombres |
| 1 | Alanya Padilla Alina Susan |
| 2 | Alarcon Castro Gustavo Claudio Andres |
| … | … |
| 1 | Baldeón Balvín Olger |
| 2 | Baldeón Sanabria Natalia Ivonne |
| … | … |
| 1 | Cachuán Cámac Miguel Fernando |
| 2 | Cajachagua Chui Jose Arturo |
| … | … |
4) Cree una función que liste todos los postulantes, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres. <Requerimiento> <ver>
| Nº | Apellidos y Nombres |
| 1 | Alanya Padilla Alina Susan |
| 2 | Alarcon Castro Gustavo Claudio Andres |
| 3 | Alarco Lama Ricardo Rafael |
| … | … |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| CREATE OR REPLACE FUNCTION f_ejemplo_4()RETURNS SETOF "record" AS$BODY$DECLARE r RECORD;BEGIN FOR r IN SELECT rownumber() "Numero",* FROM (SELECT PC.Paterno || ' ' || PC.Materno || ' ' || PC.Nombres "Apellidos y Nombres" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto ORDER BY PC.Paterno,PC.Materno,PC.Nombres) AS tb2 LOOP RETURN NEXT r; END LOOP;RETURN;END;$BODY$LANGUAGE 'plpgsql' VOLATILE;--SELECT * FROM f_ejemplo_4() AS ("Numero" INTEGER, "Apellidos y Nombres" TEXT); |
1
2
3
4
5
6
7
8
| CREATE OR REPLACE FUNCTION rownumber() RETURNS integer AS $$BEGINEXECUTE 'CREATE TEMP SEQUENCE "'||current_timestamp||'"';RETURN nextval('"'||current_timestamp||'"');EXCEPTION WHEN duplicate_table THEN RETURN nextval('"'||current_timestamp||'"');END$$ LANGUAGE 'plpgsql';;; |
5) Cree una función que reciba como parámetro el id de un contacto y que muestra la siguiente información. Se debe de mostrar todos los periodos académicos y una X en todos aquellos en la cual el contacto postuló. <ver>
| Periodo | Postuló |
| 2005-1 | X |
| 2005-2 | |
| 2006-1 | |
| 2006-2 | |
| 2007-1 | X |
6) Cree una función que muestre un resumen como el siguiente: <ver>
| Apellidos y Nombres | 2005-1 | 2005-2 | 2006-1 | 2006-2 | 2007-1 | 2007-2 | TOTAL |
| Alanya Padilla Alina Susan | X | 1 | |||||
| Alarcon Castro Gustavo Claudio Andres | X | 1 | |||||
| Alarco Lama Ricardo Rafael | X | 1 | |||||
| Altez Yanez Jasmin Gabriela | X | 1 | |||||
| Amable Salva Katerin Lisbet | X | 1 | |||||
| … |
7) Cree una función que muestre un resumen como el siguiente: <ver>
| Puntaje | 2005-1 | 2005-2 | 2006-1 | 2006-2 | 2007-1 | 2007-2 | TOTAL |
| 0 | 1 | 3 | 1 | 2 | 2 | 9 | |
| 12 | 1 | 1 | |||||
| 13 | 1 | 1 | |||||
| 14 | 1 | 1 | |||||
| 21 | 1 | 1 | 2 | ||||
| 23 | 1 | 3 | 4 | ||||
8.) Cree una función que liste todos aquellos postulantes (Apellidos, Nombres, Puntaje) cuyo puntaje es mayor o igual al puntaje promedio obtenido en todos los exámenes. <ver>
VISTAS:
9) Cree una vista que liste a
todos aquellos postulantes que no asistieron al examen. Muestre los
nombres, Apellidos, edad y género en formato (Varón/Mujer). <ver>
10) Cree una vista que liste a todos aquellos postulantes que ingresaron cuyo cumpleaños sea mañana (Entiéndase mañama como el dia después de la fecha actual). Muestre nombres y apellidos. <ver>
11) Cree una vista que muestre un resumen como el siguiente: <ver>
| Nombre | 2005-1 | 2005-2 | 2006-1 | 2006-2 | 2007-1 | TOTAL |
| Administración | 5 | 8 | 12 | 14 | 23 | 62 |
| Contabilidad | 6 | 7 | 13 | 17 | 22 | 65 |
| Ingeniería Informática | 0 | 0 | 0 | 0 | 0 | 0 |
| TOTAL | 11 | 15 | 25 | 31 | 45 | 127 |
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
| CREATE OR REPLACE VIEW v_ejemplo_11AS( SELECT AC.Nombre,SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1') THEN 1 ELSE 0 END) "2005-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-2') THEN 1 ELSE 0 END) "2005-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-1') THEN 1 ELSE 0 END) "2006-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-2') THEN 1 ELSE 0 END) "2006-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2007-1') THEN 1 ELSE 0 END) "2007-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1','2005-2','2006-1','2006-2','2007-1') THEN 1 ELSE 0 END) "TOTAL" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto INNER JOIN Admision.Carrera AC ON AC.IDCarrera=PP.IDCarrera GROUP BY AC.Nombre UNION SELECT 'TOTAL',SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1') THEN 1 ELSE 0 END) "2005-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-2') THEN 1 ELSE 0 END) "2005-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-1') THEN 1 ELSE 0 END) "2006-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2006-2') THEN 1 ELSE 0 END) "2006-2", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2007-1') THEN 1 ELSE 0 END) "2007-1", SUM(CASE WHEN PP.Ingreso='1' AND PP.IDPerAcad IN ('2005-1','2005-2','2006-1','2006-2','2007-1') THEN 1 ELSE 0 END) "TOTAL" FROM Persona.Contacto PC INNER JOIN Persona.Postulante PP ON PP.IDContacto=PC.IDContacto INNER JOIN Admision.Carrera AC ON AC.IDCarrera=PP.IDCarrera);--SELECT * FROM v_ejemplo_11; |
PROCEDIMIENTOS ALMACENADOS:
12) Cree un procedimiento almacenado que permita eliminar los postulantes registrados correspondientes a una modalidad. <ver>
1
2
3
4
5
6
7
8
9
| CREATE OR REPLACE PROCEDURE sp_ejemplo_12(p_IDModalidad varchar)ASBEGIN DELETE FROM Persona.Postulante WHERE IDModalidad=p_IDModalidad;END--EXEC sp_ejemplo_12('2');--SELECT * FROM Persona.Postulante; |
13)Cree un procedimiento almacenado que permita registrar a un nuevo contacto. <ver>
14)Cree un procedimiento almacenado que permita actualizar los datos de un postulante. <ver>
TOPIC:FUNCTION, PROCEDURE, VIEW Fuente: https://saforas.wordpress.com/2009/11/08/funciones-y-procedimientos-en-postgresql/