Creación de Procedimientos Almacenados en PostgreSQL
Resumen
Los procedimientos almacenados son una herramienta fundamental en la gestión y optimización de bases de datos. Permiten encapsular lógica de negocios y consultar la información de formas avanzadas y eficientes. En PostgreSQL, estos procedimientos son conocidos como PL/pgSQL y proporcionan altos niveles de flexibilidad y rendimiento a los desarrolladores. En este artículo, exploraremos cómo trabajar con procedimientos almacenados en PostgreSQL, enfocándonos en la creación y manejo de funciones, variables y bloques de código, así como su aplicación práctica para mejorar las operaciones en la base de datos.
¿Qué son los procedimientos almacenados en PostgreSQL?
Procedimientos almacenados, también llamados PL/pgSQL en el entorno PostgreSQL, son esencialmente bloques de código que se ejecutan en la misma base de datos. Su estructura es similar a una función en lenguajes de programación tradicionales, con una declaración inicial, uso de variables, cuerpo de código y un fin. Pueden retornar valores o no, y su principal ventaja es la capacidad de ejecutarse directamente en el servidor de la base de datos.
¿Cómo se estructura un procedimiento almacenado en PostgreSQL?
Un procedimiento almacenado en PostgreSQL se compone de tres partes principales:
Declaración: Define la función, su nombre y parámetros.
Bloque de Código: Se ejecuta y puede incluir lógica de control, operaciones con datos y otras funciones.
Fin del Procedimiento: Marca el término de la función y se define si retorna valores.
¿Cómo utilizar variables y control de flujo en PL/pgSQL?
Para gestionar los datos de manera eficiente en PL/pgSQL, es fundamental comprender el manejo de variables y el control de flujo:
Declaración de Variables: Se declara una variable con un nombre y tipo de dato. Para inicializarla, se usa :=.
Estructuras de Control: PL/pgSQL cuenta con estructuras de control como FOR, IF y LOOP para manipular datos dinámicamente.
¿Cómo implementar y ejecutar un procedimiento almacenado?
Implementar un procedimiento almacenado en PostgreSQL implica la creación de una función que encapsula el bloque de código. He aquí los pasos básicos:
Declarar la función con su nombre y tipo de retorno.
Definir las variables y la lógica de negocio en el bloque de código.
Indicar el lenguaje del procedimiento almacenado (PL/pgSQL).
¿Qué son los delimitadores de dólar ("$$") en PL/pgSQL?
En PL/pgSQL, los delimitadores de dólar ($$) se utilizan para definir el inicio y el fin de un bloque de código. Esto resuelve posibles conflictos con otros símbolos como comillas simples.
¿Cuál es el propósito de las herramientas como PGAdmin en la gestión de PL/pgSQL?
Herramientas como PGAdmin facilitan la gestión de procedimientos almacenados al proporcionar una interfaz gráfica que simplifica la creación, modificación y ejecución de las funciones en PL/pgSQL.
¿Cómo vincular una función PL/pgSQL con una tabla mediante triggers?
Los triggers (disparadores) permiten asociar funciones PL/pgSQL con tablas específicas para que se ejecuten de forma automática ante eventos como inserciones, actualizaciones o borrados.
Conclusión
El uso de procedimientos almacenados en PostgreSQL permite automatizar y optimizar la gestión de datos de forma potente y eficiente. Aprender a integrar estos elementos en las operaciones diarias de la base de datos no solo mejora el rendimiento, sino que también maximiza la seguridad y consistencia del manejo de datos. Con la práctica y un entendimiento claro de su estructura y uso, los desarrolladores pueden tomar ventaja de esta poderosa funcionalidad en PostgreSQL. ¡El mundo de la gestión de bases de datos está lleno de oportunidades para aquellos que se arman con estas habilidades!
PL Procedural language, también conocido como procedimientos almacenados, estas nos ayuda a desarrollar código directamente en el motor de bases de datos.
Estructura de un Pl es: Declaración + uso de variable+ código +fin + retorno de valores o no retorna valores.UN bloque de código se ejecuta con la palabra DO $$ BEGIN --insert código here END $$
RAISE NOTICE 'message', esta sentencia es para enviar un mensaje en el log de postgres
Retornar una tabla
Retornar una tabla.
DO$$ -Declaración de un bloque de código SQL
Estructura
DO $BODY$
BEGIN--insert código here
END$BODY$
Ejemplo de declaración de bloques de código con plpgsql
DO $$
DECLARE rec record; contador integer :=0;BEGIN--recorre tabla pasajero y lo guarda en la variable rec
FOR rec INSELECT*FROM pasajero LOOPRAISENOTICE'id: % ,Nombre: % ', rec.id,rec.nombre;contador:= contador +1;ENDLOOP;RAISENOTICE'cantidad de registros: %', contador;END$$
CREATE FUNTION - Declaración de una función SQL
CREATEFUNCTIONconsulta_usuarios()RETURNSvoidLANGUAGE'plpgsql';AS $BODY$
DECLARE rec record; contador integer :=0;BEGIN--recorre tabla pasajero y lo guarda en la variable rec
FOR rec INSELECT*FROM pasajero LOOPRAISENOTICE'id: % ,Nombre: % ', rec.id,rec.nombre;contador:= contador +1;ENDLOOP;RAISENOTICE'cantidad de registros: %', contador;END$BODY$
OTRO Ejemplo:
Retornar una tabla con plpgsql ¡¡¡¡importante!!!! es importante cual select uses para llamar la función. la función funciona de la siguiente manera en el parámetro sí se introduce NULL retorna toda la lista, si se introduce id retornará esa tupla
--FUNCIONQUERETORNAUNATABLA--Mostrar tabla con plpgsql
--https://stackoverflow.com/questions/18084936/pl-pgsql-functions-how-to-return-a-normal-table-with-multiple-columns-using-an
DROPFUNCTIONconsulta_t_pasajero(p_pasajero_id integer);CREATEORREPLACEFUNCTIONconsulta_t_pasajero(p_pasajero_id integer)RETURNSTABLE(id integer, nombre character varying, direccion_residencia character varying, fecha_nacimiento date)LANGUAGE plpgsql
AS $BODY$
BEGINIF p_pasajero_id ISNULLTHENRETURNQUERYSELECT pasajero.id, pasajero.nombre, pasajero.direccion_residencia, pasajero.fecha_nacimientoFROMpublic.pasajero;ENDIF;RETURNQUERYSELECT pasajero.id, pasajero.nombre, pasajero.direccion_residencia, pasajero.fecha_nacimientoFROMpublic.pasajeroWHERE pasajero.id= p_pasajero_id;END;$BODY$
--Retorno en forma de fila
SELECTconsulta_t_pasajero(NULL);SELECTconsulta_t_pasajero(50);--Retorno en forma de tabla
SELECT*FROMconsulta_t_pasajero(NULL);SELECT*FROMconsulta_t_pasajero(50);
Excelente resumen
Un aporte muy bueno.
Esta clase te vuela la cabeza 🤯
.
Para quienes vamos comenzando con la programación y las bases de datos PL/PgSQL es justamente fusionar los dos mundos. Para quien no sepa de programación creo que puede ser complicado darle seguimiento.
.
¿Qué es un ciclo for?
.
En general, el ciclo for(del inglés por) se usa en aquellas situaciones en las cuales queremos que una variable vaya tomando un valor de una lista definida de valores, repetidamente. La estructura base puede ser la siguiente:
.
Estructura muy general de un for:
.
forvarinvalores: haz algo
for: hace referencia a la función bucle for.
var: es una variable donde se guardarán datos.
valores: es una lista de datos, puede ser una fila o columna
haz algo: se refiere a la acción que podemos hacer con el dato guardado en la variable var.
.
--- El bucle for es uno de los más utilizados, es un ciclo que se repite haciendo la misma acción que en este ejemplo, es tomar un dato de la lista 'valores' y guardarla en la variable var.
.
Para aprender más de ciclos for con Python visiten el curso básico de Python 😄 es genial.
.
Razones para dominar PL/PgSQL
.
PL / pgSQL es fácil de aprender y fácil de usar. (Quizá no tanto pero no imposible). 💪🏽
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 incorporada 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.
El servidor de base de datos PostgreSQL puede definir que PL / pgSQL sea de confianza.
.
¿Qué más hago con PL?
.
Aplícale estructuras de control a tu base de datos:
Puedes implementar árboles de decisiones con if statments, case statements, loops statements, while loops, exit y continue.
.
Crea procedimientos almacenados con:
Create procedure and Drop procedure.
.
Funciones definidas por el usuario:
create function statement, return table, manejo de excepciones, etc.
.
Todo eso y más a detalle en este tutorial de PL/PgSQL
.
🦄 Está clase me gustó bastante pero si quieres profundizar en el uso de PL/PgSQL puedes hacer el curso de PostgreSQL para ciencia de datos.
.
También te recomiendo este tutorial, de donde saqué parte de la información de PL/PgSQL
.
Práctica Python en PostgreSQL 🐍
.
.
Este es un tutorial de práctica con una base de datos donde ensayas las partes básicas de la creación de funciones.
excelente aporte
Gracias por el aporte! muy bueno
Las funciones y los Stored procedure son un pilar dentro de las bases de datos con ayuda de los lenguajes procedurales como PLPGSQL y PL/SQL (En el caso de Oracle). Gran parte de la lógica de negocios puede ser abstraída directamente en la base de datos con ayuda de dicho lenguaje permitiendo liberar al backend de parte de este procesamiento.
Está increíble poder hacer uso de funciones directamente en las bases de datos.
Buenas Tardes.
Esta clase debería ser más compleja y con una introducción previa donde haya mayor explicación.
Coincido contigo, Diego. Yo estoy aprendiendo Postgre, aún no se programación y me siento un poco perdida.
Estoy de acuerdo, deberían haber más clases relacionadas con este tema.
Este curso está muy bien estructurado, explicado de forma clara y concisa gracias Oswaldo.
De acuerdo con tu comentario, el contenido, la estructura y el profesor dan mucha claridad en el curso.
Esta parte deberia ser un poco mas amplia, ya que es una herramienta fundamental para los desarrolladores de BD, interesante pero me parece que no muestran el verdadero poder de PG si no accedes al core de la BD.
Las funciones y procedmientos almacenados son una gran herramienta para realizar las consultas de los datos. Esto optimiza recursos y le saca el jugo al motor de base de datos. Muchas empresas colocan la logica del negocio en la base de datos y asi pueden mejorar el rendimiento y cualquier arreglo en las consultas son mas sencillo de implementar.
Interesante!
si hay mucho flujo de datos hacia la BD esta se puede convertir en un cuello de botella, no hay q abusar
Ayuda a desarrollar código directamente sobre la base de datos.
Se componen de nombres de funciones, declaraciones y bloques de código.
Estructura Básica.
[ <label> ]
[ DECLARE
declarations]
BEGIN
Statements
END [label];
DO $$
BEGIN
RAISE NOTICE 'Algo está pasando';
END
$$
DECLARE
rec record := ; // Se escribe el nombre de la variable y luego el tipo de dato que retorna, para darle un valor inicial utilizamos := ¿por qué? porque el igual solo está reservado para las consultas.
DO $$
DECLARErec record;// Record almacena datos.counter integer :=0;// EnteroBEGINFOR rec INSELECT*FROM passenger LOOP// rec almacenará todos los datos de la tabla passengerRAISENOTICE'Un pasajero se llama %', rec.name;// El % será reemplazado por la variable que vamos a mostrar. Para acceder al nombre usamos rec.namecounter:= counter +1;ENDLOOP;RAISENOTICE'La cantidad de pasajeros es: %', counter;END$$
Postgres usando algunas librerías puede soportar otros lenguajes de programación, como python, c++, sql básico entre otros. Por ello, cuando hagamos una función debemos indicar qué lenguaje de programación estamos utilizando.
CREATEFUNCTIONtestPL()RETURNSvoid// No regresa nada.AS $$
DECLARErec record;counter integer :=0;BEGINFOR rec INSELECT*FROM passenger LOOPRAISENOTICE'Un pasajero se llama %', rec.name;counter:= counter +1;ENDLOOP;RAISENOTICE'La cantidad de pasajeros es: %', counter;END$$
LANGUAGEPLPGSQL;SELECTtestPL();
En las tablas no mostrará ningún valor, pero si se mira desde la pestaña de mensajes, se podrán ver el mensaje de los pasajeros y cuántos hay.
Para actualizar una función se utiliza CREATE OR REPLACE FUNCTION <nombreFunción>(). Sin embargo, si queremos actualizar el tipo de dato que retorna será imposible hacer dicha actualización, porque el tipo de dato no es actualizable, para ello, debemos borrar la función con DROP FUNCTION <nombreFunción>(); y luego volver a crear la función.
DROPFUNCTIONtestPL();// Borramos la función anterior, para poder actualizar el tipo de dato que retornaCREATEORREPLACEFUNCTIONtestPL()RETURNS integer
AS $$
DECLARErec record;counter integer :=0;BEGINFOR rec INSELECT*FROM passenger LOOPRAISENOTICE'Un pasajero se llama %', rec.name;counter:= counter +1;ENDLOOP;RAISENOTICE'La cantidad de pasajeros es: %', counter;RETURN counter;END$$
LANGUAGEPLPGSQL;SELECTtestPL();// Nos muestra en una celda la cantidad de pasajeros que hay en nuestra tabla, adicionalmente, en la pestaña mensajes muestra las consultas al nombre de estos pasajeros y al final cuántos hay.
CREANDO PL USANDO FUNCIONES DE PGADMIN.
En el menú desplegable de la izquierda vamos a functions, click derecho, CREATE, le damos el nombre, en Definition se le tiene que asignar un tipo de retorno, en Code pegamos el bloque de código que queremos insertar, en este caso es:
Las configuraciones por defecto nos funcionan bien cómo están, así que no moveremos nada por ahora, depende de los requerimientos de la base de datos y de los proyectos en los que trabajemos requeriremos estas funciones, pero por ahora, la configuración por defecto es óptima.
En la pestaña SQL nos muestra el código que se va a ejecutar, podemos ver que postgres coloca sus propios signos pesos, razón por la cual no es necesario que peguemos en la pestaña código dichos signos.
Guardamos y vemos que la función ha sido creada.
Gran resumen!!
Para listar tus funciones en la linea de comandos puedes usar:
\df
Para ver todo el código de tu función usas show function:
\sf <name_func>
Excelente,
muchas gracias por la info :) !
Me voy a saltar esta clase porque no entiendo nada...
Hola Vicente, que es lo que no entiendes de la clase?
Si te saltas una clase lo mas probable es que te pierdas en la próxima, haz tu mayor esfuerzo de comprenderla
Cual es la diferencia entre las functions y los procedures ?
las functions pueden retornar valores
PL
Para retornar el área de un triangulo (useless pues no tiene relación con la db, pero sirve para ir practicando)
CREATEORREPLACEFUNCTIONtriangle_area(base float, hight float)RETURNS real
AS$BODY$
DECLARE area float; half float :=0.5;BEGINarea:= base*hight*half;RAISENOTICE'The area is: %', area;RETURN area;END;$BODY$
LANGUAGE'plpgsql';SELECT*FROMtriangle_area(7,5);
Pero que tenga que ver con la Base de Datos, como que eso no va, no estamos calculando figuras
que genial, me encanta
Información resumida de esta clase
#EstudiantesDePlatzi
PL= Procedimientos almacenados
Una PL tiene que ver con consulta a base de datos y es su principal función
:= es en Postgre la asignación de variables y solamente el = está reservado para las consultas
El % con la función RAISE nos permite agregar una variable externa
No podemos tener dos funciones PL iguales
También podemos crear en Postgre está PL usando las herramientas
Tenemos que tener cuidado con el lenguaje que le asignamos a nuestra PL
es necesario hacer PL en postgres ? porque créanme no entiendo nada
generalmente es mejor que la lógica este en la aplicación web
Ya casi DataTreeGardener :P
DO $$
DECLARE rec record
BEGINFOR rec INSELECT*FROM pasajero LOOPRAISENOTICE'Un pasajero se llama %', rec.nombre;ENDLOOP;END$$;
Pequeña correccion:
rec record;
Este tema se me hizo un poco complicado de entender, así que intente hacer un resumen para los que, como yo, son nuevos en estos temas. Espero que les ayude.
Muy buenos tus apuntes !
PL/SQL
PostgreSQL también tiene su propio lenguaje de programación, conocido como PL/pgSQL. Es un lenguaje similar a PL/SQL y permite a los desarrolladores crear procedimientos almacenados, funciones y desencadenadores dentro de la base de datos de PostgreSQL.
PL/pgSQL es altamente integrado con la base de datos de PostgreSQL y es una excelente opción para aplicaciones de bases de datos complejas que requieren una gran cantidad de procesamiento de datos dentro de la base de datos. Al igual que PL/SQL, PL/pgSQL permite la manipulación de datos en la base de datos de una manera eficiente y escalable
CREATEORREPLACEFUNCTIONobtener_nombres(edad integer)RETURNSSETOF text AS $$
DECLARE nombre text;BEGINFOR nombre INSELECT nombre FROM clientes WHERE edad = edad LOOPRETURNNEXT nombre;ENDLOOP;RETURN;END;$$ LANGUAGE plpgsql;
Esta función toma un argumento de edad y devuelve un conjunto de nombres de clientes con esa edad. La función utiliza un bucle FOR para iterar sobre los resultados de la consulta SQL y devolver cada nombre individualmente.
Puedes llamar a esta función en la terminal de PostgreSQL con el siguiente comando:
scssCopy code
SELECT*FROMobtener_nombres(30);
El resultado sería un conjunto de nombres de clientes con edad 30.
Creo que esta clase se pudo haber partido en dos
Que tanto se usa esto de PL en el dia a dia de la vida real?
Trabajé en empresas dónde por usar un backend compilado les quedaba mas fácil hacer su lógica del negocio en PL y era más del 50% de su core.
