PostgreSQL, parte 3: Funciones y Procedimientos

Introducción

El lenguaje de procedimientos PL/pgSQL agrega muchos elementos de procedimientos, por ejemplo, estructuras de control, bucles y cálculos complejos, para ampliar el SQL estándar. Le permite desarrollar funciones complejas y procedimientos almacenados en PostgreSQL que pueden no ser posibles usando SQL simple.

El lenguaje de procedimiento PL/pgSQL es similar a Oracle PL/SQL . Las siguientes son algunas de las razones para aprender PL/pgSQL:

• PL/pgSQL es fácil de aprender y fácil de usar.
• PL/pgSQL viene con PostgreSQL por defecto. Las funciones definidas por el usuario y los procedimientos almacenados desarrollados en PL/pgSQL se pueden utilizar como cualquier función integrada y procedimiento almacenado.
• PL/pgSQL hereda todos los tipos, funciones y operadores definidos por el usuario.
• PL/pgSQL tiene muchas características que le permiten desarrollar funciones complejas y procedimientos almacenados.
• PL/pgSQL se puede definir para que el servidor de base de datos de PostgreSQL confíe en él.

Las funciones creadas con PL/pgSQL se pueden usar en cualquier lugar donde se puedan usar las funciones integradas. Por ejemplo, es posible crear funciones de cálculo condicional complejas y luego usarlas para definir operadores o usarlas en expresiones de índice.

En PostgreSQL 9.0 y versiones posteriores, PL/pgSQL está instalado de forma predeterminada. Sin embargo, sigue siendo un módulo cargable, por lo que los administradores especialmente conscientes de la seguridad podrían optar por eliminarlo.

Cómo crear un campo updated_at que automaticamente registre la fecha cuando el registro se modifica con PostgreSQL?

Para crear un campo updated_at que se actualice automáticamente cuando se modifica un registro por ejemplo en una base de datos MySQL, puedes utilizar la función CURRENT_TIMESTAMP junto con el atributo ON UPDATE.

Aquí está el código para crear una tabla con un campo updated_at:

CREATE TABLE ejemplo (
   id INT PRIMARY KEY,
   nombre VARCHAR(50),
   descripcion VARCHAR(255),
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

En este ejemplo, el campo updated_at se establece con un valor predeterminado de la fecha y hora actual utilizando la función CURRENT_TIMESTAMP, y también se agrega el atributo ON UPDATE CURRENT_TIMESTAMP, lo que significa que se actualizará automáticamente con la fecha y hora actual cada vez que se modifique el registro.

Sin embargo, para crear un campo updated_at que se actualice automáticamente cuando se modifica un registro en PostgreSQL, se debe utilizar una función llamada trigger.

Aquí está el código para crear una tabla con un campo updated_at:

CREATE TABLE ejemplo (
   id SERIAL PRIMARY KEY,
   nombre VARCHAR(50),
   descripcion VARCHAR(255),
   updated_at TIMESTAMP
);

En este ejemplo, el campo updated_at se deja sin valor predeterminado ya que será actualizado automáticamente a través de la función trigger.

A continuación, puedes crear una función trigger que se active cada vez que se modifique un registro en la tabla ejemplo, y actualizará automáticamente el valor del campo updated_at con la fecha y hora actual.

CREATE OR REPLACE FUNCTION actualizar_updated_at()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Esta función trigger utiliza la función NOW() para obtener la fecha y hora actuales y actualizar el campo updated_at del registro modificado.

Por último, puedes crear el trigger que se activará cada vez que se realice una operación UPDATE en la tabla ejemplo, y ejecutará la función trigger actualizar_updated_at().

CREATE TRIGGER trigger_actualizar_updated_at
BEFORE UPDATE ON ejemplo
FOR EACH ROW
EXECUTE FUNCTION actualizar_updated_at();

Este trigger se activará antes de que se actualice cualquier registro en la tabla ejemplo, y ejecutará la función trigger actualizar_updated_at(), que actualizará el campo updated_at con la fecha y hora actuales.

Con estos pasos, el campo updated_at se actualizará automáticamente cada vez que se modifique un registro en la tabla ejemplo. Puedes ajustar los nombres de la tabla y los campos para que se adapten a tus necesidades específicas.

Artículos previos:

PostgreSQL conn, parte 2 técnicas avanzadas, en ChatGPT (OpenAI)

PostgreSQL conn en ChatGPT (OpenAI), Psycopg v2 o v3?