No tienes acceso a esta clase

¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera

PLPGSQL: Stored procedures

13/34
Recursos

Aportes 28

Preguntas 2

Ordenar por:

¿Quieres ver más aportes, preguntas y respuestas de la comunidad?

  • 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.

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.

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.

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

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();

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();
Lets pray for PALESTINE😶‍🌫️

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?

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.
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** 1. **No devuelven valores:** A diferencia de las funciones, un procedimiento no utiliza `RETURN` para devolver datos. 2. **Ejecutados con** `CALL`**:** Para invocarlos se usa la sentencia `CALL`. 3. **Manejo de transacciones:** Los procedimientos pueden realizar operaciones de control de transacciones como `COMMIT` y `ROLLBACK`. 4. **Reutilización:** Reducen la repetición de código en consultas y operaciones frecuentes. 5. **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ística**Stored ProcedureFunction**Retorno 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?** 1. **Centralización de lógica:** Evitan replicar lógica de negocio en el lado del cliente. 2. **Eficiencia:** Reducen el tráfico entre la aplicación y la base de datos. 3. **Seguridad:** Ocultan detalles de las tablas y columnas. 4. **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.
PL/pgSQL: ## Procedural Language / PostgreSQL
Una sugerencia que le hago a platzi es que cuando el profesor se equivoque debería corregir el video completo para que no tengamos que perdertanto tiempo corrigiendo al mismo tiempo que elaboramos el ejercicio
hola, como puedo obtener la fecha de creación o modificación de una función.

PL/pgSQL es un lenguaje de programación procedural específicamente diseñado para PostgreSQL. Se utiliza comúnmente para escribir procedimientos almacenados (stored procedures) que contienen lógica de negocios y se almacenan directamente en la base de datos.

Ejemplo Práctico:

Crear un procedimiento almacenado que calcule la suma de dos números:

CREATE OR REPLACE FUNCTION suma_dos_numeros(a INT, b INT)
RETURNS INT AS $$

DECLARE
    resultado INT;

BEGIN
    resultado := a + b;
    RETURN resultado;

END $$ LANGUAGE plpgsql;

Esta funcion crea una columna que convierte la columna timestamp de rentas en una columna de fecha: ```js CREATE OR REPLACE FUNCTION agregar_date() RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN ALTER TABLE rentas ADD COLUMN IF NOT EXISTS renta_date DATE; UPDATE rentas SET renta_date = DATE(fecha_renta); END; $$; SELECT agregar_date() ```

PL/pgSQL se basa en el lenguaje PL/SQL de Oracle y se ha diseñado para tener una sintaxis similar, lo que permite a los desarrolladores que están familiarizados con PL/SQL de Oracle trabajar fácilmente con PostgreSQL.

Si en dado caso hacen una FUNCTION y la ejecutaron y luego haces un SELECT y funcionó como en el caso de la clase.
Y luego vuelven a modificar la FUNCTION sin ejecutar y hacen un SELECT NO se verá reflejado el cambio hasta que vuelvas a ejecutar la FUNCTION con su nuevo comando o doncicion o nuevo código.

Me acaba de pasar y experimenté y no tuve que borrar manualmente la tabla “aaa” como en el caso del video. 😃

Corriganme si me equivoqué porfas!

Buenisimo el concejo de los procedimientos y las funcione, no lo enteni anteriormente… se parece mucho a Pascal realmente

create or replace function moon()
returns void
language plpgsql
as $$
begin
drop table if exists moon1;
create table moon1
(moon1 char(5) constraint f_key primary key);
drop table if exists moon2;
create table moon2
(moon2 char(5) constraint f1_key primary key);
end
$$;

select moon();

buenard

Excelente, gracias