Desarrollo de Funciones Almacenadas PostgreSQL

Introducción

En este laboratorio, aprenderá a desarrollar funciones almacenadas de PostgreSQL. Seguirá el proceso de definir una función almacenada básica, agregar parámetros de entrada con valores predeterminados, ejecutar la función dentro de una consulta y, finalmente, eliminar una función no utilizada para mantener su base de datos organizada.

Definir una Función Almacenada Básica

En este paso, aprenderá a definir una función almacenada básica en PostgreSQL. Las funciones almacenadas son bloques de código reutilizables que realizan una tarea específica y pueden ejecutarse dentro de la base de datos.

Primero, abra una terminal y conéctese a la base de datos PostgreSQL utilizando la herramienta de línea de comandos psql. Realizará todas las operaciones de base de datos dentro de esta shell de psql.

sudo -u postgres psql

Ahora debería ver el prompt de PostgreSQL, que se ve como postgres=#.

Ahora, cree una función llamada get_total_products que devuelva un valor entero fijo. Esta función simple le ayudará a comprender la sintaxis básica. Ejecute el siguiente comando SQL en la shell de psql:

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

Analicemos este comando:

CREATE FUNCTION get_total_products(): Esto define una nueva función llamada get_total_products sin parámetros de entrada.
RETURNS INTEGER: Esto especifica que la función devolverá un único valor del tipo de dato INTEGER.
AS $$ ... $$: La cadena delimitada por signos de dólar $$ se utiliza para encerrar el cuerpo de la función. Esta es una práctica común en PostgreSQL para evitar problemas con las comillas simples dentro del código de la función.
BEGIN ... END;: Este bloque contiene la parte ejecutable de la función.
RETURN 100;: Esta es la lógica de nuestra función, que simplemente devuelve el entero 100.
LANGUAGE plpgsql: Esto especifica que la función está escrita en plpgsql, el lenguaje procedural de PostgreSQL.

Después de ejecutar el comando, PostgreSQL confirmará la creación de la función:

CREATE FUNCTION

Para verificar que la función se creó, puede llamarla en una instrucción SELECT:

SELECT get_total_products();

La salida mostrará el valor devuelto por la función:

 get_total_products
--------------------
                100
(1 row)

Esto confirma que su primera función almacenada está funcionando correctamente.

Crear una Función con Parámetros

Las funciones almacenadas se vuelven más potentes cuando pueden aceptar parámetros de entrada. En este paso, creará una nueva función que toma dos números como entrada y devuelve su suma.

Asegúrese de que todavía está en la shell de psql del paso anterior. Ahora, cree una función llamada add_numbers que acepte dos parámetros enteros.

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

La sintaxis es similar al paso anterior, con una diferencia clave en la firma de la función:

add_numbers(a INTEGER, b INTEGER): Esto define dos parámetros, a y b, ambos de tipo INTEGER, que deben proporcionarse cuando se llama a la función.

Después de ejecutar el comando, verá la confirmación de CREATE FUNCTION.

Ahora, pruebe la función proporcionando dos números como argumentos:

SELECT add_numbers(15, 25);

La función sumará los dos números y devolverá el resultado:

 add_numbers
-------------
          40
(1 row)

También puede usar funciones con valores de parámetros predeterminados. Creemos una nueva función greet_user donde el mensaje de saludo tiene un valor predeterminado.

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

Aquí, CREATE OR REPLACE actualizará la función si ya existe. Al parámetro greeting se le asigna un valor predeterminado de 'Hello'. El operador || se utiliza para la concatenación de cadenas.

Pruebe la función proporcionando solo el parámetro requerido username:

SELECT greet_user('Alex');

La función utiliza el saludo predeterminado:

       greet_user
------------------------
 Hello, Alex!
(1 row)

Ahora, llámela de nuevo, pero esta vez, proporcione un saludo personalizado:

SELECT greet_user('Alex', 'Welcome');

La salida ahora muestra su mensaje personalizado:

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

Ejecutar una Función en una Consulta

Un uso común de las funciones almacenadas es realizar cálculos sobre datos de tablas. En este paso, creará una tabla, la poblará con datos y luego usará una función en una consulta sobre esa tabla.

Primero, cree una tabla simple llamada products para almacenar nombres y precios de productos.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

Verá el mensaje de confirmación CREATE TABLE.

A continuación, inserte algunos datos de ejemplo en la tabla products:

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

Verá INSERT 0 3, lo que indica que se insertaron tres filas.

Ahora, creemos una función para calcular el precio con impuesto sobre las ventas. Esta función tomará un precio como entrada y devolverá el precio incluyendo un impuesto del 7%.

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

Después de crear la función, puede usarla directamente en una consulta SELECT sobre la tabla products. Esta consulta mostrará el precio original y el precio con impuestos para cada producto.

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

La salida mostrará los resultados de la llamada a la función para cada fila:

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

Esto demuestra cómo puede encapsular la lógica de negocio dentro de una función y aplicarla a sus datos.

Eliminar una Función Almacenada

Es una buena práctica eliminar los objetos de base de datos que ya no se necesitan. En este paso, aprenderá cómo eliminar o "descartar" una función almacenada de su base de datos. Eliminaremos la función get_total_products creada en el primer paso.

Primero, puede listar las funciones en su base de datos para confirmar que get_total_products existe.

\df

Verá una lista de funciones, incluida get_total_products.

Para eliminar la función, use el comando DROP FUNCTION. Debe especificar el nombre de la función. Si la función tiene parámetros, necesitaría especificar sus tipos, pero dado que get_total_products no tiene ninguno, puede usar solo el nombre.

DROP FUNCTION get_total_products();

PostgreSQL confirmará la acción:

DROP FUNCTION

Ahora, si lista las funciones nuevamente con \df, verá que get_total_products ya no está en la lista.

También es importante especificar los tipos de argumentos si la función está sobrecargada (es decir, múltiples funciones con el mismo nombre pero diferentes parámetros). Por ejemplo, para eliminar la función add_numbers, debe especificar sus parámetros enteros:

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Finalmente, para limpiar su entorno, elimine la tabla products creada en el paso anterior.

DROP TABLE products;

Esto concluye el laboratorio. Para salir de la shell de psql, escriba \q y presione Enter.

Resumen

En este laboratorio, aprendió los fundamentos del desarrollo de funciones almacenadas en PostgreSQL. Creó una función básica, la mejoró con parámetros y valores predeterminados, aplicó una función en una consulta contra datos de tablas y, finalmente, limpió la base de datos eliminando las funciones y tablas. Estas habilidades son esenciales para crear soluciones de bases de datos más modulares y eficientes.

Desarrollo de Funciones Almacenadas en PostgreSQL