Cursores (Cursors) y Funciones (Functions) PLpg/SQL en PostgreSQL

Un cursor es el nombre que recibe un apuntador (pointer) de solo lectura hacia un conjunto de datos ( resultset) que se obtiene de una consulta SQL asociada, para los cursores pensemos en términos de arreglos similar a los arreglos de un lenguaje de programación, los cursores nos sirven para procesar una a una las filas que componen un conjunto de resultados en vez de trabajar con todos los registros como en una consulta tradicional de SQL.

Los cursores pueden declararse con una consulta SQL sin parámetros o con parámetros, en donde el tamaño del conjunto de datos depende del valor de los parámetros de la consulta. Así por ejemplo declaramos:

Los cursores pueden emplearse dentro de funciones PL/SQL para que las aplicaciones que accedan a PostgreSQL puedan utilizarlos más de una vez.

Como ejemplo tenemos una base de datos llamada myinvoices, que contiene las siguientes tablas:

invoices

invoicedetails

Ahora como ejemplo de un cursor utilizado dentro de una función, declaramos una función utilizando un cursor con parámetros para devolver el total de facturas emitidas dentro de un rango de fechas.

Existen dos errores comunes cuando trabajamos con cursores:

1. Si tratas de abrir un cursor que ya se encuentra abierto PostgreSQL enviará un mensaje de “cursor [name] already in use”
2. Si tratas de ejecutar FETCH en un cursor que no ha sido abierto, PostgreSQL enviará un mensaje de “cursor [name] is invalid”

Cuando utilizamos el comando FETCH obtenemos una por una las filas del conjunto de resultados después de cada fila procesada el cursor avanza a la siguiente fila y la fila procesada puede ser entonces utilizada dentro de una variable.

Consultamos los datos de la tabla invoices

Ejecutamos la función, usando los siguientes argumentos:

Obtenemos los siguientes resultados:

Entendiendo Transacciones (Transactions) con ADO .NET y PostgreSQL

En .NET las transacciones son representadas por la clase Transaction que implementa la interfaz IDbTransaction definida dentro del ensamblado System.Data esta interfaz proporciona los métodos:

Commit: Confirma la transacción y persiste los datos.

Rollback: Regresa los datos a un estado anterior a la transacción.

Esta interfaz se utiliza para crear clases Transaction asociadas con un proveedor especifico, así para SQL Server tenemos SqlTransaction, para Oracle OracleTransaction y para PostgreSQL NpgsqlTransaction.

La ventaja de crear transacciones en .NET y no en la bases de datos es proporcionar a la aplicaciones la capacidad de las transacciones en caso de utilizar una base de datos que no proporcione o soporte esa característica.
Como ejemplo escribimos un programa en MonoDevelop que utiliza las tablas: Invoices e InvoiceDetails que se utilizaron en esta entrada este programa utiliza una transaction y 4 comandos SQL con los que guarda una factura (invoice) con dos detalles (invoice details), actualizando el total de la factura conforme a la cantidad de productos y su precio.

using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using NpgsqlTypes;
using System.Data;
using System.Globalization;


namespace MonoTransExamples
 {
  class Program
  {
  static string connString = "Server=127.0.0.1;Port=5432;Database=myinvoices;
User ID=postgres;Password=Pa$$W0rd";

  static string commandText1 = "INSERT INTO invoices(invoice_number,invoice_date,
invoice_total)" + "VALUES(:number,:date,:total)";

  static string commandText2 = "SELECT MAX(invoice_id) FROM invoices";
  static string commandText3 = "INSERT INTO invoicedetails
(invoice_id,invoiced_description,invoiced_quantity,invoiced_amount)" +
"VALUES(:id,:description,:quantity,:amount)";

static string commandText4 = "UPDATE invoices SET invoice_total =
 :total WHERE invoice_id = :id";
static void Main(string[] args)
{
    bool success = false;
    int recordsAffected = 0;
    Invoice invoice = new Invoice { 
        Invoice_number = 2099,
        Invoice_date = new DateTime(2013,01,29,6,6,6,100,Calendar.CurrentEra)
    };
    Invoicedetails[] details = { 
    new Invoicedetails{
        Invoice = invoice,
        Invoiced_description = "walkie-talkie 22-Channel",
        Invoiced_quantity = 3,
        Invoiced_amount = 19.99M
    },
    new Invoicedetails{
        Invoice = invoice,
        Invoiced_description = "2 GB SD Memory Card",
        Invoiced_quantity = 4,
        Invoiced_amount = 6.99M
    }
    };
    NpgsqlTransaction transaction = null;
    NpgsqlConnection conn = null;
    try
    {
    conn = new NpgsqlConnection(connString);
    conn.Open();
    transaction = conn.BeginTransaction();
using (NpgsqlCommand cmd1 = new NpgsqlCommand(commandText1, conn, transaction))
{
cmd1.CommandType = CommandType.Text;
cmd1.Parameters.Add("number", NpgsqlDbType.Integer, 4).Value = invoice.Invoice_number;
cmd1.Parameters.Add("date", NpgsqlDbType.Timestamp).Value = invoice.Invoice_date;
cmd1.Parameters.Add("total", NpgsqlDbType.Money).Value = invoice.Invoice_total;
      recordsAffected = cmd1.ExecuteNonQuery();
        }
        Console.WriteLine("{0} invoiced inserted",recordsAffected);
        if (recordsAffected > 0)
        {
 using (NpgsqlCommand cmd2 = new NpgsqlCommand(commandText2, conn, transaction))
            {
       cmd2.CommandType = CommandType.Text;
       invoice.Invoice_id = Convert.ToInt32(cmd2.ExecuteScalar());
            }
        Console.WriteLine("Invoice Id {0} ",invoice.Invoice_id);
        }
        if (invoice.Invoice_id > 0)
        {
        recordsAffected = 0;
        foreach (Invoicedetails invd in details)
        {
        invd.Invoice.Invoice_id = invoice.Invoice_id;
         using (NpgsqlCommand cmd3 = new NpgsqlCommand(commandText3, conn, transaction))
         {
 cmd3.CommandType = CommandType.Text;
 cmd3.Parameters.Add("id", NpgsqlDbType.Integer, 4).Value = invd.Invoice.Invoice_id;
 cmd3.Parameters.Add("description", NpgsqlDbType.Varchar, 512).Value = invd.Invoiced_description;
 cmd3.Parameters.Add("quantity", NpgsqlDbType.Smallint).Value = invd.Invoiced_quantity;
 cmd3.Parameters.Add("amount", NpgsqlDbType.Money).Value = invd.Invoiced_amount;
        recordsAffected += cmd3.ExecuteNonQuery();
                }
        invoice.Invoice_total += invd.Invoiced_amount * invd.Invoiced_quantity;
            }
Console.WriteLine("Total: {0} ,{1} records affected ",invoice.Invoice_total,recordsAffected);
        }
        if (recordsAffected == details.Length)
        {
      using (NpgsqlCommand cmd4 = new NpgsqlCommand(commandText4, conn, transaction)) {
 cmd4.CommandType = CommandType.Text;
 cmd4.Parameters.Add("total",NpgsqlDbType.Money).Value = invoice.Invoice_total;
 cmd4.Parameters.Add("id",NpgsqlDbType.Integer).Value = invoice.Invoice_id;
      recordsAffected = cmd4.ExecuteNonQuery();
            }
Console.WriteLine("Updated invoice {0} with total {1} ",
invoice.Invoice_id,invoice.Invoice_total);
        }
        if(recordsAffected > 0)
                success = true;
    }catch(NpgsqlException ex){
        Console.WriteLine("Error {0} ", ex.Message);
    }finally{
        if (success)
            transaction.Commit();
        else
            transaction.Rollback();
        if (conn != null)
            if (conn.State == ConnectionState.Open)
                conn.Close();
    }
    Console.WriteLine("Done");
    Console.ReadLine();
}
}

    class Invoice {
    public int Invoice_id { set; get; }
    public int Invoice_number { set; get; }
    public DateTime Invoice_date { set; get; }
    public Decimal Invoice_total { set; get; }

    }

    class Invoicedetails {
    public int Invoiced_id { set; get; }
    public Invoice Invoice { set; get; }
    public string Invoiced_description { set; get; }
    public int Invoiced_quantity { set; get; }
    public Decimal Invoiced_amount { set; get; }
    }
}

Este programa asocia una transacción con una conexión abierta.

conn = new NpgsqlConnection(connString);
conn.Open();
transaction = conn.BeginTransaction();

Se crea cada uno de los comandos SQL dentro del alcance de la transacción

using (NpgsqlCommand cmd1 = new NpgsqlCommand(commandText1, conn, transaction))
using (NpgsqlCommand cmd2 = new NpgsqlCommand(commandText2, conn, transaction))
using (NpgsqlCommand cmd3 = new NpgsqlCommand(commandText3, conn, transaction))
using (NpgsqlCommand cmd4 = new NpgsqlCommand(commandText4, conn, transaction)) 

Si todos los comandos se ejecutan correctamente se llama al método Commit() de lo contrario se llama al método Rollback y se cierra la conexión.

  if (success)
            transaction.Commit();
        else
            transaction.Rollback();
        if (conn != null)
            if (conn.State == ConnectionState.Open)
                conn.Close();

Es importante recordar que la transacción queda pendiente hasta que no se confirme (commit) o se cancele (rollback), si se cierra la conexión mediante el método Close se ejecuta un rollback en todas las transacciones pendientes.

Entendiendo Transacciones con PostgreSQL.

Hay operaciones en los sistemas de bases de datos (DBMS) que no pueden expresarse como una única operación SQL sino como el resultado de un conjunto de dos o más operaciones SQL, cuyo éxito depende de que cada una de esas operaciones se ejecute correctamente ya que si una de ellas falla se considera que toda la operación fallo.
El control de transacciones es una característica fundamental de cualquier DBMS (como PostgreSQL,MS SQL Server ú Oracle) esto permite agrupar un conjunto de operaciones o enunciados SQL en una misma unidad de trabajo discreta , cuyo resultado no puede ser divisible ya que solo se considera el total de operaciones completadas, si hay una ejecución parcial el DBMS se encarga de revertir esos cambios para dejar la información consistente.

Una transacción tiene cuatro características esenciales conocidas como el acrónimo ACID:

• Atomicity(Atomicidad): Una transacción es una unidad atómica o se ejecutan las operaciones múltiples por completo o no se ejecuta absolutamente nada, cualquier cambio parcial es revertido para asegurar la consistencia en la base de datos.
• Consistency (Consistencia): Cuando finaliza una transacción debe dejar todos los datos sin ningún tipo de inconsistencia, por lo que todas las reglas de integridad deben ser aplicadas a todos los cambios realizados por la transacción, o sea todas las estructuras de datos internas deben de estar en un estado consistente.
• Isolation (Aislamiento o independencia): Esto significa que los cambios de cada transacción son independientes de los cambios de otras transacciones que se ejecuten en ese instante, o sea que los datos afectados de una transacción no están disponibles para otras transacciones sino hasta que la transacción que los ocupa finalice por completo.
• Durability (Permanencia): Después de que las transacciones hayan terminado, todos los cambios realizados son permanentes en la base de datos incluso si después hay una caída del DBMS.

Las transacciones en PostgreSQL utilizan las siguientes palabras reservadas:

BEGIN: Empieza la transacción

SAVEPOINT [name]: Le dice al DBMS la localización de un punto de retorno en la transacción si una parte de la transacción es cancelada. El DBMS guarda el estado de la transacción hasta este punto.

COMMIT: Todos los cambios realizados por las transacciones deben ser permanentes y accesibles a las demás operaciones del DBMS.

ROLLBACK [savepoint]: Aborta la actual transacción todos los cambios realizados deben ser revertidos.

Para poner en práctica estos comandos utilizaremos dos tablas relacionadas Invoices y InvoiceDetails.

Agregamos un par de registros, cada uno dentro de una transacción en el primer registro el commit (confirmación) se realiza de forma automática al terminar la transacción con el comando END.

En el segundo registro utilizamos el comando COMMIT de forma explicita para hacer los cambios permanentes.

Ahora insertamos un nuevo registro y eliminamos un par pero en vez de confirmar la transacción con COMMIT deshacemos los cambios y regresamos los registros a su estado original, utilizando ROLLBACK.

Aquí otro ejemplo del uso de ROLLBACK.

En el siguiente bloque de PL/SQL anónimo vamos a utilizar los comandos anteriores además del comando SAVEPOINT el cuál permite deshacer parcialmente los cambios hechos dentro de una transacción y no toda la transacción por completo.
Persistimos entonces solo los cambios antes del SAVEPOINT, los cambios realizados después serán revertidos por el comando ROLLBACK.