Trabajando con Store Procedures PL/pgSQL en PostgreSQL

Los lenguajes de procedimientos (procedural languages) han extendido la funcionalidad de las bases de datos proporcionando al lenguaje SQL cierto flujo de control similar al de un lenguaje de programación.

Cada fabricante tiene su propia implementación de un lenguaje de procedimiento basado en SQL, así Microsoft SQL Server tiene Transact-SQL, Oracle tiene PL/SQL y así sucesivamente una lista de bases de datos con su correspondiente lenguaje de procedimiento. En el caso de PostgreSQL se tienen varios lenguajes que pueden usarse como lenguajes de procedimiento entre ellos tenemos a PL/Tcl, PL/Perl, PL/Python,C y como opción predeterminada PL/pgSQL.

Las ventajas de ejecutar funciones del lado del servidor o Store Procedures con PL/pgSQL son las siguientes:

1. Extensibilidad: PL/pgsql es como un lenguaje de programación incluye la asignación y evaluación de variables y la posibilidad de hacer iteraciones y cálculos más complejos que con SQL.
2. Seguridad: Ayuda a evitar ciertos ataques con SQL Injection ya que al utilizar parámetros evita la construcción de comandos SQL utilizando la concatenación de cadenas.
3. Rapidez: Son ejecutados más rápidamente que las consultas SQL individuales ya que después de la primera ejecución el plan de ejecución se mantiene en memoria y el código no tiene que ser analizado ni optimizado nuevamente.
4. Programación modular: similar a los procedimientos y funciones en los lenguajes de programación, lo que evita que se tengan planas de sentencias SQL.
5. Reutilización: de código una función puede ejecutarse dentro de distintos Store Procedures.
6. Rendimiento: un Store Procedure puede contener decenas de sentencias SQL que son ejecutadas como una sola unidad de golpe, a diferencia de las sentencias SQL individuales donde hay que esperar a que cada una sea procesada.

Mediante los siguientes ejemplos mostraremos el uso de Store Procedures utilizando PL/pgSQL.

Creamos una base de datos de ejemplo llamada Catalogs con el siguiente comando desde el Shell.

    $ createdb Catalogs

Fig 1. Creando la base de datos de ejemplo

Revisamos los lenguajes de procedimiento instalados en la base de datos, revisamos que PL/pgSQL se encuentre instalado con el siguiente comando.

    $ createlang -l Catalogs

Fig 2. Revisando la instalación del lenguaje PLSQL

Si no se encuentra, entonces ejecutamos el siguiente comando para instalarlo, esto siempre como administrador del servidor:

    $ createlang –U postgres plpgsql Catalogs

Si está instalado entonces abrimos un editor de texto y creamos un archivo llamado catalogs.sql donde escribiremos los comandos para crear las tablas de ejemplo y los Store Procedures para administrar los registros de cada una de las tablas.

Para la creación de las tablas escribimos lo siguiente:

En acuerdo con la llave foránea definida en cada tabla, debe existir un país para poder crear un estado, así mismo debe de existir un estado para poder crear una ciudad, entonces creamos unos registros en la tabla países

Fig 3. Insertando los registros en la tabla

Supongamos que estas tablas van a utilizarse en un sistema donde sea obligatorio que los nombres de país, estado y ciudad, se almacenen teniendo la primera letra mayúscula o en notación Camel Case,(en este caso los países quedaron guardados con letras minúsculas de manera intencional, con el fin de mostrar un Store Procedure).

Creamos entonces una función para aplicar esta regla a los datos de la tabla countries.

La función se define con el siguiente código:

Lo aplicamos de la siguiente manera para actualizar los registros.

Fig 4. Actualizando los registros utilizando la función UpperCamelCase

Una vez creada en el servidor se encuentra disponible para cualquier transformación que queramos aplicar sobre cualquier cadena.

Fig 5. Ejecutando la función UpperCamelCase

Ahora escribiremos las funciones para insertar registros en cada una de las tablas.

Básicamente la estructura de un Store Procedure es la siguiente:

CREATE FUNCTION [nombre de la función] ([parámetros separados por comas]) RETURNS [el tipo de dato que regresa] AS
'DECLARE aquí se definen las variables que se usarán.
BEGIN indica el inicio de la función.
SELECT INTO este comando permite que los resultados de las consultas sean asignados a variables. 
(no debe de confundirse con SELECT [columnas] INTO)
RETURN Sale de la función y regresa el tipo de dato que se declaro después de la palabra RETURNS del CREATE FUNCTION
END indica el fin de la función
' LANGUAGE indica con que lenguaje esta escrita la función, puede ser un lenguaje de procedimiento 
(plpgsql) o de consulta (SQL).

Vemos que en cada Store Procedure, reutiliza la función UpperCamelCase(varchar) que habíamos previamente creado.

Esto porque un Store Procedure puede llamar a otro Store Procedure que se encuentre disponible.

En las siguientes imágenes los resultados de la ejecución de cada Store Procedure.

Fig 6. Ejecucción del procedimiento InsertState

Fig 7. Ejecucción del procedimiento InsertCity

Fig 8. Comprobando la aplicación de los procedimientos en los datos

Utilizando los objetos NpgsqlConnection y NpgConnectionStringBuilder para PostgreSQL con MonoDevelop

Hoy día es difícil imaginarse un sistema informático que no haga uso de fuente de datos para persistir información.
Entre estas fuentes de datos se encuentran:

• Sin estructura: Archivos que no tienen un orden lógico. (Cartas, memos)
• Estructurado, sin jerarquía: Contienen datos agrupados por separadores o indicadores de inicio y fin (archivos de acceso secuencial, archivos separados por tabuladores ó comas)
• Jerárquica: tienen una estructura de nodos anidados. (XML)
• Bases de datos relacionales:
• Objetos: Los datos están organizados como objetos.

Debido a esta variedad, las clases de ADO para acceder y extraer cuentan con un diseño independiente y portable de la implementación de la fuente de datos que se utilice.
Desde luego, primeramente debemos tener acceso a la fuente de datos antes de ejecutar operaciones de consulta o modificación.
Para lograr este propósito usamos un objeto proveedor de conexión especifico según la fuente de datos a utilizar, cada uno de estos objetos son equivalentes en funcionalidades generales por derivar de la clase DBConnection.
Aunque estos objetos son equivalentes en la funcionalidad general, cada uno tiene código específico para su fuente de datos, como se muestra en la tabla siguiente.

+-----------------+-------------------+
| Fuente de datos |Objeto de conexión |
+-----------------+-------------------+
| SQL Server      | SqlConnection     |
+-----------------+-------------------+
| Suportan OLE DB | OleDbConnection   |
+-----------------+-------------------+
| Oracle          | Oracleconnection* |
+-----------------+-------------------+
| PostgreSQL      | NpgsqlConnection  |
+-----------------+-------------------+
*Depende de que el cliente de Oracle se encuentre instalado.

El objeto NpgsqlConnection

Un objeto conexión representa una conexión física a una fuente de datos, por lo que una de las mejores recomendaciones es conectarse y cerrar las conexiones abiertas en cuanto dejen de utilizarse.
ConnectionStrings (Cadenas de conexión)
Antes de trabajar con cualquiera de los objetos de conexión es indispensable proporcionarle la Connection String (cadena de conexión) , esta Connection String es propia de cada objeto Connection y cada objeto Connection es propio de cada fuente de datos con la que trabajemos.
La cadena de conexión es una serie de parámetros y valores separados por (;) y aunque cada Connection String cambie según el proveedor de Base de datos, hay ciertos parámetros que son siempre requeridos, como:

+---------------------------+-------------------------------------------------+
|Data Source o Server       | El lugar donde se encuentra la fuente de datos: |
                            | servidor, máquina, lugar de red, IP, directorio,|
                            | archivo.                                        |
+---------------------------+-------------------------------------------------+
DataBase o Initial Catalog  | El nombre de la base de datos a usar.           |
+---------------------------+-------------------------------------------------+
|User ID                    | El usuario para acceder.                        |
+---------------------------+-------------------------------------------------+
 Password                   | El password del usuario para aceder             |
+---------------------------+-------------------------------------------------+
Integrated Security         | Si es true se utiliza la seguridad de Windows 
                            | para acceder, si es false se debe de proporcionar 
                            | el user y password para acceder.
+---------------------------+-------------------------------------------------+
Timeout o Connection Timeout| El tiempo que debe esperar la aplicación para   |
                            | que el servidor le asigne una conexión.         |
                            | El valor predeterminado es de 15 segundos       |
+---------------------------+-------------------------------------------------+
Pooling                     | Crea un pool de conexiones para la cadena de    |
                            | conexión si no existe, caso contrario           |
                            | asigna una conexión de un pool existente.       |
+---------------------------+-------------------------------------------------+
MinPoolSize                 | Número mínimo de conexiones por pool, valor     |
                            | predeterminado 1                                |
+---------------------------+-------------------------------------------------+
MaxPoolSize                 | Número máximo de conexiones por pool, valor     |
                            | máximo permitido 100                            |
+---------------------------+-------------------------------------------------+

El sitio Connection Strings.com contiene ejemplos y una amplia referencia a cada uno de los parámetros y valores según la fuente de datos.

El objeto NpgsqlConnectionStringBuilder

Hay una clase que nos ayuda a evitar errores de sintaxis al momento de construir cadenas de conexión, esto para no tener recordar cada una de las opciones en el caso de trabajar con diferentes bases de datos. Esta clase es similar a las clases Connection, hay una clase DbConnectionStringBuilder de donde surgen las objetos ConnectionStringBuilder específicos para cada cadena de conexión de cada objeto Connection.
Derivados de la clase DbConnectionStringBuilder, existe la clases NpgsqlConnectionStringBuilder la cual encapsula una ConnectionString (cadena de conexión) para PostgreSQL, la cual demostraremos en el siguiente programa junto con el uso de la clase NpgsqlConnection respectivamente.

Abrimos MonoDevelop, creamos una nueva solución GTK# y agregamos unos controles al formulario para que su aspecto luzca como en la siguiente imagen.

Como utilizaremos el proveedor de datos de PostgreSQL para .NET debemos agregarlo a la solución, hacemos click derecho en la solución(recomiendo la versión 2.x).

Aparecerá la ventana para agregar/quitar las referencias a los ensamblados instalados en el GAC o para buscarlos en el sistema de archivos.

Una vez agregado correctamente, se mostrará el ensamblado en la solución.

Dentro del método para dar funcionalidad al botón de aceptar, mostramos la utilización de la clase NpgsqlConnectionStringBuilder como ayuda para crear la cadena de conexión.

También dentro de este método tenemos el código para la creación de la conexión al servidor.

using(NpgsqlConnection conn = new NpgsqlConnection(connString.ToString())){
conn.Open();
if(conn.State ==  System.Data.ConnectionState.Open)
  MessageBox("Conexión exitosa",MessageType.Info);
}

Si descargamos el código completo del proyecto, al compilarlo correctamente y ejecutarlo,nos pedirá teclear los parámetros para una conexión hacia un servidor PostgreSQL.

Si los parámetros son correctos y el servidor se encuentra disponible entonces la aplicación mostrará el mensaje siguiente: