PostgreSQL: Consultas Enumeradas

Este es el Post #5 del Topic: Guía Práctica de PostgreSQL

SUGERENCIA:
Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla.

Descargar Script: DBAdmision.sql

Ahora es el momento de hablar un poco sobre la elaboración de consultas que retornen records con una columna adicional que enumere todas las filas.

En postgres 8.3 e versiones inferiores no hay algo parecido a la función ROW_NUMBER de SLQServer (¿Lo has usado alguna vez?) , así que nos la arreglaremos de cualquier   manera para resolver este dilema.

En este ejemplo continuaremos como de costumbre haciendo uso de la base de datos DBAdmision.sql y nos dedicaremos a explicar la consulta número 4 elaborada en el post PostgreSQL: Funciones, Procedimientos y Vistas.

«…Cree una función que liste todos los postulantes, la lista debe estar enumerada y ordenada por apellido paterno, materno y nombres.» <Requerimiento> <ver>

Primero debemos crear una función que nos retorne una tipo de dato numérico, la llamaremos rownumber, para ello también se realizará la creación temporal de un objeto SEQUENCE, cuyo nombre será el timestamp actual, por ejemplo: SELECT current_timestamp retorna algo como: «2009-11-17 06:18:53.057964-05». La función nextval realizará el retorno del valor siguiente de dicha secuancia, lo que hará que por cada llamada que reciba  el valor irá incementando de uno un uno.

CREATE OR REPLACE FUNCTION rownumber() RETURNS integer AS $$
BEGIN
EXECUTE 'CREATE TEMP SEQUENCE "'||current_timestamp||'"';
RETURN nextval('"'||current_timestamp||'"');
EXCEPTION WHEN duplicate_table THEN RETURN nextval('"'||current_timestamp||'"');
END
$$ LANGUAGE 'plpgsql';

La función anterior se creará por defecto en el esquema público.

Como la función crea una secuencia temporal hace posible que al finalizar la ejecución de la consulta la secuencia se elimine y por lo tanto la numeración vuelva a reiniciarse en cada ejecución.

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 tb1

A partir de la versión 8.4 de PostgreSQL, podemos ahorrarnos esfuerzos para construir esta consulta, ya que en ésta versión si exíste la función row_number, aunque habrá que hacer uso de Window Functions(Funciones Ventanas) que veremos en otro post.