Contenido del curso
Procedimientos y Funciones en PostgreSQL: Creación y Uso
¿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
Estudiante![Francisco Garcia [C6]](https://static.platzi.com/media/avatars/avatars/garciafran_c845c2fe-2ea4-42db-a305-a7f4ef13b0fd.jpeg){kind=small}
Francisco Garcia [C6]
Estudiante
Mateo Posada
Estudiante
Frank Sócrates Valencia Pinto
Estudiante
Luis Rogelio Reyes Hernandez
Estudiante
Héctor José Ospino Velásquez
Estudiante
José Rodrigo Arana Hi
Estudiante
Wilford Giuseppe Camargo Quesada
Estudiante
Alfonso Andres Zapata Guzman
Estudiante
Andres Sanchez
Estudiante
JAVIER SANTIAGO SALGADO
Estudiante
Katherine Rodríguez Sánchez
Estudiante
Isay Humberto Lucas Hernandez
Estudiante
Jorge Guillen
Estudiante
Andres Alvarado
Estudiante
Santiago Ortiz Ceballos
Estudiante
Ricardo Gomez
Estudiante
Wilson Cortes Alvarez
Estudiante
Elisa Zamarron Muñoz
Estudiante
Carlos Javier Guevara Contreras
Estudiante
Creck Brayan Mauri Pastor Paredes
Estudiante
Abril Darynka Tapia Sosa
EstudianteCreck Brayan Mauri Pastor Paredes
Estudiante
Sergio Javier Lopez Olivera
Estudiante
Sofia Valeria Toro Chambi
Estudiante
Luis Enrique Sanchez Piñerua
Estudiante
EDWING ALFONSO ARENAS RUEDA
Estudiante
Fabian Cubillos
Estudiante
Mario Alexander Vargas Celis
EstudianteGracias por el resumen
gracias por el aporte
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();
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.
Excelente aporte!!
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:
SQL StandardPLPGSQL 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:
-- 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.
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
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.
13. PLPGSQL: Stored procedures
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();
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();
¿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?
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.
Lets pray for PALESTINE😶🌫️
Please ban this guy
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
Buen ejemplo, complementar con los triggers
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.
¿Entonces para sentencias SELECT siempre hay que utilizar funciones debido a que estas me regresan valores?
create [or replace] procedure procedure_name(parameter_list) language plpgsql as $$ declare -- variable declaration begin -- stored procedure body end; $$
alguien tiene la base de datos del profesor o donde lo puedo conseguir?
Hola, la base de datos se encuentra en una de las clases más adelante
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)
Como se puede colocar parametros en los procedimientos y funciones?
Hola! Esto fue lo que encontre:
CREATE FUNCTION myFuncion(variable int) RETURNS int LANGUAGE plpgsql; AS $$ BEGIN RETURN variable + 1; END; $$
# 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.
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
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
RETURN para devolver datos.CALL: Para invocarlos se usa la sentencia CALL.COMMIT y ROLLBACK.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?
Los stored procedures en PL/pgSQL son herramientas poderosas que facilitan la creación de sistemas robustos, eficientes y fáciles de mantener.