Procedimientos y Funciones en PostgreSQL: Creación y Uso

Clase 13 de 34 • Curso de PostgreSQL Aplicado a Ciencia de Datos

Resumen

¿Qué son los procedimientos y funciones en PL/PgSQL?

En el fascinante mundo de la programación, el lenguaje PL/PgSQL se presenta como una extensión poderosa de SQL estándar, permitiendo un control más detallado y eficiente de las operaciones en bases de datos PostgreSQL. Este lenguaje es capaz de simplificar operaciones repetitivas y automatizar procesos a través de la creación de procedimientos y funciones. Pero, ¿cuáles son sus diferencias y cómo se implementan?

¿Cómo funcionan los procedimientos en PL/PgSQL?

Los procedimientos, en términos sencillos, son una serie de pasos o instrucciones dentro de un programa que ejecutan tareas específicas y, a diferencia de las funciones, no regresan ningún valor. Son útiles cuando la intención es modificar el estado de la base de datos sin necesidad de obtener una respuesta.

En PL/PgSQL, crear un procedimiento es bastante sencillo. Aquí tienes un ejemplo de cómo se puede implementar:

CREATE OR REPLACE PROCEDURE prueba_procedimiento()
LANGUAGE plpgsql
AS $$
BEGIN
    DROP TABLE IF EXISTS tabla_prueba;
    CREATE TABLE tabla_prueba(
        columna VARCHAR(5),
        CONSTRAINT pk_tabla_prueba PRIMARY KEY (columna)
    );
END;
$$;

En este código, cada vez que se ejecute el procedimiento prueba_procedimiento, eliminará la tabla tabla_prueba si existe, y la creará nuevamente con una columna específica.

¿Qué diferencia hay entre procedimientos y funciones?

La diferencia clave entre procedimientos y funciones en PL/PgSQL es el retorno de valores. Mientras que los procedimientos ejecutan tareas sin devolver un resultado, las funciones siempre regresan un valor, lo que les proporciona una mayor flexibilidad y aplicación en cálculos o transformaciones de datos.

Por ejemplo, al crear una función sencilla que realiza tareas similares a nuestro procedimiento anterior, se añadiría un tipo de retorno:

CREATE OR REPLACE FUNCTION prueba_funcion()
RETURNS void AS $$
BEGIN
    DROP TABLE IF EXISTS tabla_funcion;
    CREATE TABLE tabla_funcion(
        columna VARCHAR(5),
        CONSTRAINT pk_tabla_funcion PRIMARY KEY (columna)
    );
END;
$$ LANGUAGE plpgsql;

Como puedes observar, esta función usa RETURNS void ya que no necesita devolver un valor específico.

¿Cómo se utilizan en la práctica?

La implementación de procedimientos y funciones es clave para optimizar tareas repetitivas y mejorar la eficiencia en la gestión de la base de datos. Si necesitas ejecutar el mismo bloque de código varias veces o realizar acciones complejas que se repitan con frecuencia, encapsular esa lógica en un procedimiento o función te permitirá mantener tu código organizado y más fácil de gestionar.

La práctica con ejemplos concretos, como los proporcionados aquí, es fundamental para dominar PL/PgSQL. A través del ensayo y error, podrás desarrollar la habilidad de decidir cuándo desplegar un procedimiento y cuándo usar una función, dependiendo de lo que el escenario requiera.

¡Sigue aprendiendo y explorando las maravillosas posibilidades que PL/PgSQL te ofrece!

Angel Francisco Flores Ayala

student•

Funciones → Son más avanzadas. Regresan tipos de datos. Tienen más flexibilidad. No son estándar de SQL, se tiene que usar el lenguaje PLPGSQL.
Procedures → Integran lógica a la sentencias SQL. Se han ido incluyendo en el estándar SQL. No regresan ningún valor.

Francisco Garcia [C6]

student•

Gracias por el resumen

Mateo Posada

student•

gracias por el aporte

Frank Sócrates Valencia Pinto

student•

Stored Procedure:

CREATE OR REPLACE PROCEDURE test_drpcreate_procedure()
LANGUAGE SQL
AS $$
	DROP TABLE IF EXISTS aaa;
	CREATE TABLE aaa (bbb char(5) CONSTRAINT firstkey PRIMARY KEY);
$$;
CALL test_drpcreate_procedure();

Function

CREATE OR REPLACE FUNCTION test_dropcreate_function()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
	DROP TABLE IF EXISTS aaa;
	CREATE TABLE aaa(bbb char(5) CONSTRAINT firstkey PRIMARY KEY, ccc char(5));
	DROP TABLE IF EXISTS aaab;
	CREATE TABLE aaab (bbba char(5) CONSTRAINT secondkey PRIMARY KEY,ccca char(5));
END
$$;
SELECT test_dropcreate_function();

Luis Rogelio Reyes Hernandez

student•

PLPGSQL es un lenguaje ampliado derivado desde el SQL pudiendo ejecutar codigo SQL estandar y funciones especificas de este lenguaje

Existen

Store procedures Integran lógica a la sentencias SQL. Se han ido incluyendo en el estándar SQL. No regresan ningún valor.
Functions Son mas avanzadas estan hechas en el lenguaje de PLPGSQL, Regresan tipos de datos. Tienen más flexibilidad. No son estándar de SQL.

Héctor José Ospino Velásquez

student•

Excelente aporte!!

José Rodrigo Arana Hi

student•

PLPGSQL: Stored procedures

PLPGSQL - Procedural Language PostgreSQL

Lenguaje que Postgres creó para hacer programación más ordenada y procedural y tiene un componente muy fuerte para hacer triggers y store procedures .

Un procedure o procedimiento:

es una serie de pasos (similar a una función)
Tienen la característica de que NO regresa ningún valor.
Son parte de SQL Standard

PLPGSQL es un superset, lenguaje ampliado del Standard SQL , donde se pueden hacer todas las sentencias SQL normales o estandar, pero por encima de ello, tiene funciones muy específicas para hacer ciertas cosas, como las funciones, procedures o triggers.

Las funciones:

son mucho más avanzadas
permiten más flexibilidad
retornan tipos de datos
NO son estándar, por lo que necesitaremos PLPGSQL.

-- PROCEDURE
create or replace PROCEDURE test_dropcreate_procedure()
LANGUAGE SQL
AS $$
    DROP TABLE if exists aaa;
    CREATE TABLE aaa(
        bbb varchar(5) CONSTRAINT first PRIMARY KEY;
    );
$$;

CALL test_dropcreate_procedure();

-- FUNCTION
CREATE OR REPLACE FUNCTION test_dropcreate_function()
RETURNS void -- Regresa un vacío, como un procedure
LANGUAGE plpgsql
AS $$ --necesitaremos begin y end siempre para hacer la funcion
    BEGIN
        DROP TABLE if exists aaa;
        CREATE TABLE aaa(bbb char(5) CONSTRAINT firstkey PRIMARY KEY, ccc char(5));
        DROP TABLE if exists aaab;
        CREATE TABLE aaab(bbba char(5) CONSTRAINT secondkey PRIMARY KEY, ccca char(5));
    END
$$;

SELECT test_dropcreate_function();

Las PRIMARY KEYS deben de ser únicas en toda la DB.

Wilford Giuseppe Camargo Quesada

student•

No se si esta será una clase de transición, pero esta clase es muy diferente de las otras de este profesor, se me hace que fue my rápido como si fuera un repaso de procedure y funciones en PLPGSQL

Alfonso Andres Zapata Guzman

student•

Si man, en efecto ya hay un curso de PostgreSQL en el cual se ve a mas detalle varios de estos temas, y esta antes que este en la ruta de data science, se asume que ya el estudiante lo vio.

Andres Sanchez

student•

13. PLPGSQL: Stored procedures

Postgres creó para hacer más estructurado y ordenado
Procedure: serie de pasos que no extrae ningún valor para regresar.
Las funciones hacen lo mismo que las procedure, pero sí retornan algo.

CREATE OR REPLACE PROCEDURE test_drpcreate_procedure()
LANGUAGE SQL
AS $$
	DROP TABLE IF EXISTS aaa;
	CREATE TABLE aaa (bbb char(5) CONSTRAINT firstkey PRIMARY KEY);

$$;

CALL test_drpcreate_procedure();

CREATE OR REPLACE FUNCTION test_dropcreate_function()
RETURNS VOID
LANGUAGE plpgsql
AS  $$
BEGIN
	DROP TABLE IF EXISTS aaa;
	CREATE TABLE aaa (bbb char(5) CONSTRAINT firstkey PRIMARY KEY, ccc char(5));
	DROP TABLE IF EXISTS aaab;
	CREATE TABLE aaab (bbba char(5) CONSTRAINT secondkey PRIMARY KEY, ccca char(5));
END

$$;

SELECT test_dropcreate_function();

JAVIER SANTIAGO SALGADO

student•

PROCEDURE

CREATE OR REPLACE PROCEDURE test_drpcreate_procedure()
LANGUAGE SQL
AS $$
	DROP TABLE IF EXISTS aaa;
	CREATE TABLE aaa (bbb char(5) CONSTRAINT firstkey PRIMARY KEY)
$$;

CALL test_drpcreate_procedure();

FUNCTION

CREATE OR REPLACE FUNCTION test_dropcreate_function()
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
	DROP TABLE IF EXISTS aaa;
	CREATE TABLE aaa (bbb char(5) CONSTRAINT firstkey PRIMARY KEY, ccc char(5) );
	DROP TABLE IF EXISTS aaab;
	CREATE TABLE aaab (bbba char(5) CONSTRAINT secondkey PRIMARY KEY, ccca char(5) );
	
END
$$;

SELECT test_dropcreate_function();

Katherine Rodríguez Sánchez

student•

¿Como se pueden usar store procedures para poblar una nueva tabla reproducible todos los dias, es decir con un job que se actualice todos los dias en la noche?

Isay Humberto Lucas Hernandez

student•

Se puede crear un stored procedure que realice la tarea deseada y a través de un scheduler ejecutarlo configurando la frecuencia de ejecución.

Jorge Guillen

student•

Lets pray for PALESTINE😶‍🌫️

Andres Alvarado

student•

Please ban this guy

Santiago Ortiz Ceballos

student•

Diferencias STORE PROCEDURE y FUNCTIONS STORE PROCEDURE: no regresan valor y hacen parte del estándar SQL FUNCTIONS: regresan valores, son mas flexibles y son propias de PostgreSQL/PLPGSQL

Ricardo Gomez

student•

Buen ejemplo, complementar con los triggers

Wilson Cortes Alvarez

student•

Los nombres de los objetos son únicos en el esquema de base de datos, por eso no se permite nombrar dos o mas con la misma descripción.

Elisa Zamarron Muñoz

student•

¿Entonces para sentencias SELECT siempre hay que utilizar funciones debido a que estas me regresan valores?

Carlos Javier Guevara Contreras

student•

create [or replace] procedure procedure_name(parameter_list) language plpgsql as $$ declare -- variable declaration begin -- stored procedure body end; $$

Creck Brayan Mauri Pastor Paredes

student•

alguien tiene la base de datos del profesor o donde lo puedo conseguir?

Abril Darynka Tapia Sosa

student•

Hola, la base de datos se encuentra en una de las clases más adelante

Creck Brayan Mauri Pastor Paredes

student•

me respondo :) https://drive.google.com/file/d/1oE7A4z_D2o-udn_yflwZ03uN3cA0gVpr/view

Sergio Javier Lopez Olivera

student•

Les dejo estos 10 ejercicios nivel principiante para practicar lo visto en esta clase:

1. Función Simple: "Hola Mundo"

Objetivo: Crear tu primera función que no recibe parámetros y devuelve un texto fijo. Aprenderás la sintaxis más básica de CREATE FUNCTION, cómo especificar el tipo de dato de retorno (RETURNS) y cómo usar la palabra clave RETURN.

Documentación: CREATE FUNCTION

2. Función con Parámetros de Entrada (IN)

Objetivo: Crear una función que reciba dos números enteros como parámetros y devuelva su suma. Aprenderás a definir parámetros de entrada (IN) y a utilizarlos dentro de la función para realizar un cálculo.

Documentación: Argumentos de Funciones

3. Procedimiento para Insertar Datos

Objetivo: Crear un procedimiento que inserte una nueva fila en una tabla. Necesitarás una tabla simple (por ejemplo, usuarios con id y nombre). Aprenderás la sintaxis de CREATE PROCEDURE y cómo ejecutar una sentencia INSERT dentro de él. La principal diferencia es que un procedimiento no devuelve un valor directamente.

Documentación: CREATE PROCEDURE

4. Función que Devuelve un Único Valor de una Tabla

Objetivo: Crear una función que reciba el ID de un producto y devuelva su precio. Necesitarás una tabla de productos con id y precio. Aprenderás a realizar una consulta SELECT dentro de una función y a guardar el resultado en una variable para luego retornarlo.

Documentación: Funciones que Devuelven un Solo Valor

5. Función con Manejo de Fechas

Objetivo: Crear una función que calcule la edad de una persona a partir de su fecha de nacimiento. Aprenderás a trabajar con tipos de dato DATE, usar la función AGE() de PostgreSQL y devolver un tipo de dato INTEGER.

Documentación: Funciones y Operadores de Fecha/Hora

6. Procedimiento con Parámetros de Salida (OUT)

Objetivo: Crear un procedimiento que reciba el ID de un empleado y devuelva su nombre y salario a través de parámetros de salida. Aprenderás a definir parámetros OUT y a asignarles valores dentro del cuerpo del procedimiento.

Documentación: Parámetros OUT

7. Función con Lógica Condicional (IF/THEN/ELSE)

Objetivo: Crear una función que reciba un número y devuelva el texto 'Par' o 'Impar' según corresponda. Aprenderás a utilizar la estructura de control IF/THEN/ELSE para ejecutar código basado en una condición.

Documentación: Estructuras de Control Condicionales

8. Función para Manipular Cadenas de Texto

Objetivo: Crear una función que reciba un nombre y un apellido, y los devuelva como una sola cadena con el formato "Apellido, Nombre". Aprenderás a concatenar cadenas de texto y a usar funciones básicas de manipulación de strings.

Documentación: Funciones y Operadores de Cadenas

9. Procedimiento que Emite un Mensaje (RAISE NOTICE)

Objetivo: Crear un procedimiento que actualice el precio de un producto y muestre un mensaje de confirmación en la consola usando RAISE NOTICE. Aprenderás a dar feedback sobre las operaciones que se realizan dentro de un procedimiento.

Documentación: Reporte de Mensajes y Errores

10. Función que Devuelve un Tipo Compuesto (un registro de tabla)

Objetivo: Crear una función que reciba el ID de un cliente y devuelva toda la fila (el registro completo) de ese cliente. Aprenderás a especificar el nombre de una tabla como tipo de dato de retorno, lo que facilita devolver conjuntos de datos estructurados.

Documentación: Retorno desde una Función (Composite Types)

Sofia Valeria Toro Chambi

student•

Como se puede colocar parametros en los procedimientos y funciones?

Luis Enrique Sanchez Piñerua

student•

Hola! Esto fue lo que encontre:

CREATE FUNCTION myFuncion(variable int) 
RETURNS int 
LANGUAGE plpgsql;
AS $$
BEGIN
RETURN variable + 1;
END;
$$

EDWING ALFONSO ARENAS RUEDA

student•

# estructura de una FUNCION en PLPGSQL

- Las funciones (FUNCTION) se usan cuando queremos que devuelvan un valor o conjunto de valores.

CREATE OR REPLACE FUNCTION nombre_funcion(parametros)

RETURNS tipo_de_dato AS $$

BEGIN

-- Cuerpo de la función

RETURN valor;

END;

$$ LANGUAGE plpgsql;

- en SQL se llama o usa con el comando SELECT

- Aplicaciones:

* Cálculos matemáticos.

* Conversión de datos.

* Validaciones.

# Estructura de un PROCEDIMIENTO en PL/pgSQL

Los procedimientos (PROCEDURE) son usados cuando no devuelven valores y generalmente

realizan operaciones como INSERT, UPDATE, DELETE.

CREATE OR REPLACE PROCEDURE nombre_procedure(parametros)

LANGUAGE plpgsql AS $$

BEGIN

-- Cuerpo del procedimiento

END;

$$;

- en SQL se llama o usa con el comando CALL

- Aplicaciones:

* Manipulación de datos (INSERT, UPDATE, DELETE).

* Procesos en lotes.

* Administración de transacciones (COMMIT, ROLLBACK).

# ¿Cuándo usar cada uno?

- Usa FUNCTION cuando necesitas un resultado en una consulta.

- Usa PROCEDURE cuando necesitas ejecutar acciones en la base de datos sin devolver valores.

Fabian Cubillos

student•

PL/pgSQL (Procedural Language/PostgreSQL) es un lenguaje procedimental para PostgreSQL, el popular sistema de gestión de bases de datos relacional de código abierto. PL/pgSQL es similar a otros lenguajes de procedimientos como PL/SQL en Oracle y Transact-SQL en SQL Server, lo que facilita la escritura de procedimientos almacenados, funciones y desencadenadores dentro de PostgreSQL.

Características de PL/pgSQL

Lenguaje Procedural: Permite el uso de estructuras de control como bucles y condicionales, lo que facilita la creación de lógica compleja.
Variables y Constantes: Se pueden declarar y usar variables y constantes dentro de los procedimientos y funciones.
Manejo de Errores: Incluye estructuras para capturar y manejar errores.
Compatible con SQL: Integra SQL directamente, permitiendo ejecutar consultas y operaciones DML/DDL dentro de los bloques de código PL/pgSQL.
Desencadenadores (Triggers): Permite crear triggers que se ejecutan en respuesta a eventos específicos en las tablas.
Procedimientos Almacenados: Facilita la creación de procedimientos que pueden ser llamados repetidamente y pueden incluir lógica compleja.

Mario Alexander Vargas Celis

student•

En PL/pgSQL, las stored procedures (procedimientos almacenados) son bloques de código reutilizables que ejecutan una serie de operaciones en la base de datos. A diferencia de las funciones, los procedimientos no devuelven un valor, sino que se usan para realizar tareas como insertar, actualizar, eliminar registros o ejecutar operaciones administrativas.

Características principales de los procedimientos almacenados en PL/pgSQL

No devuelven valores: A diferencia de las funciones, un procedimiento no utiliza RETURN para devolver datos.
Ejecutados con CALL: Para invocarlos se usa la sentencia CALL.
Manejo de transacciones: Los procedimientos pueden realizar operaciones de control de transacciones como COMMIT y ROLLBACK.
Reutilización: Reducen la repetición de código en consultas y operaciones frecuentes.
Optimización: Mejoran el rendimiento al ejecutar la lógica cerca de los datos.

Sintaxis básica

La sintaxis para crear un procedimiento almacenado es:

CREATE PROCEDURE nombre_procedimiento(parámetros) LANGUAGE plpgsql AS $$ BEGIN -- Código PL/pgSQL aquí END; $$;

Para ejecutar el procedimiento:

CALL nombre_procedimiento(parámetros);

Ejemplo 1: Procedimiento básico

Este procedimiento inserta un registro en una tabla llamada usuarios:

CREATE PROCEDURE insertar_usuario(nombre TEXT, edad INT) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO usuarios (nombre, edad) VALUES (nombre, edad); END; $$;

Uso:

CALL insertar_usuario('Mario', 30);

Ejemplo 2: Procedimiento con manejo de transacciones

En este ejemplo, el procedimiento registra un pedido y actualiza el inventario. Si algo falla, se revierte la transacción.

CREATE PROCEDURE registrar_pedido(id_producto INT, cantidad INT, cliente TEXT) LANGUAGE plpgsql AS $$ BEGIN -- Registrar el pedido INSERT INTO pedidos (producto_id, cantidad, cliente, fecha) VALUES (id_producto, cantidad, cliente, NOW());

-- Actualizar el inventario UPDATE inventario SET stock = stock - cantidad WHERE producto_id = id_producto;

-- Validar stock negativo IF (SELECT stock FROM inventario WHERE producto_id = id_producto) < 0 THEN RAISE EXCEPTION 'Stock insuficiente para el producto %', id_producto; END IF; END; $$;

Uso:

CALL registrar_pedido(1, 5, 'Cliente1');

Ejemplo 3: Procedimiento con bucles y lógica

Un procedimiento que actualiza precios de productos por categoría con un incremento porcentual:

CREATE PROCEDURE actualizar_precios(categoria_id INT, incremento FLOAT) LANGUAGE plpgsql AS $$ BEGIN FOR producto IN SELECT id, precio FROM productos WHERE categoria_id = categoria_id LOOP UPDATE productos SET precio = precio * (1 + incremento) WHERE id = producto.id; END LOOP; END; $$;

Uso:

CALL actualizar_precios(2, 0.10); -- Incrementa precios un 10% para categoría 2

Manejo de errores en procedimientos

Puedes capturar errores con bloques EXCEPTION para manejar situaciones inesperadas.

Ejemplo:

CREATE PROCEDURE ejemplo_error() LANGUAGE plpgsql AS $$ BEGIN -- Intentar insertar un registro INSERT INTO tabla_no_existente (columna) VALUES (1); EXCEPTION WHEN undefined_table THEN RAISE NOTICE 'La tabla no existe. Operación cancelada.'; END; $$;

Uso:

CALL ejemplo_error();

Comparación: Stored Procedures vs. Functions

CaracterísticaStored ProcedureFunctionRetorno de valoresNoSí (valor único o conjunto de datos)Uso de CALL o SELECTCALLSELECT o dentro de consultas SQLControl de transaccionesSí (COMMIT, ROLLBACK)No (depende del contexto de ejecución)Usos principalesOperaciones administrativas o complejasCálculos y operaciones que devuelven datos

¿Por qué usar stored procedures en PL/pgSQL?

Centralización de lógica: Evitan replicar lógica de negocio en el lado del cliente.
Eficiencia: Reducen el tráfico entre la aplicación y la base de datos.
Seguridad: Ocultan detalles de las tablas y columnas.
Flexibilidad: Perfectas para operaciones administrativas complejas y tareas repetitivas.

Los stored procedures en PL/pgSQL son herramientas poderosas que facilitan la creación de sistemas robustos, eficientes y fáciles de mantener.