En aquest tema, explorarem PL/pgSQL, el llenguatge procedimental natiu de PostgreSQL, així com altres llenguatges procedimentals que es poden utilitzar dins de PostgreSQL. Els llenguatges procedimentals permeten escriure funcions i procediments que poden executar-se dins de la base de dades, proporcionant una gran flexibilitat i potència per a la manipulació de dades i la lògica de negoci.
Continguts
Introducció a PL/pgSQL
PL/pgSQL és un llenguatge procedimental dissenyat per a PostgreSQL que permet escriure funcions i procediments emmagatzemats. Aquest llenguatge és similar a PL/SQL d'Oracle i proporciona estructures de control de flux, variables, i la capacitat de gestionar errors.
Característiques clau de PL/pgSQL:
- Variables i tipus de dades: Permet declarar variables i utilitzar tipus de dades de PostgreSQL.
- Control de flux: Suporta estructures de control com
IF,LOOP,WHILE,FOR, etc. - Gestió d'errors: Proporciona mecanismes per capturar i gestionar errors.
- Integració amb SQL: Permet executar consultes SQL dins de les funcions.
Creació de funcions amb PL/pgSQL
Sintaxi bàsica
CREATE OR REPLACE FUNCTION nom_funció(paràmetres)
RETURNS tipus_de_retorn AS $$
BEGIN
-- Cos de la funció
RETURN valor;
END;
$$ LANGUAGE plpgsql;Exemple pràctic
Creem una funció que calcula el descompte aplicat a un preu:
CREATE OR REPLACE FUNCTION calcular_descompte(preu NUMERIC, descompte NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN preu - (preu * descompte / 100);
END;
$$ LANGUAGE plpgsql;Explicació del codi
CREATE OR REPLACE FUNCTION: Crea una nova funció o reemplaça una existent.calcular_descompte(preu NUMERIC, descompte NUMERIC): Defineix el nom de la funció i els seus paràmetres.RETURNS NUMERIC: Especifica el tipus de retorn de la funció.BEGIN ... END;: Defineix el cos de la funció.RETURN preu - (preu * descompte / 100);: Calcula el descompte i retorna el resultat.LANGUAGE plpgsql: Especifica que la funció està escrita en PL/pgSQL.
Control de flux en PL/pgSQL
PL/pgSQL proporciona diverses estructures de control de flux per gestionar la lògica dins de les funcions.
Estructura IF
IF condició THEN
-- Codi a executar si la condició és certa
ELSIF altra_condició THEN
-- Codi a executar si l'altra condició és certa
ELSE
-- Codi a executar si cap condició és certa
END IF;Exemple pràctic
CREATE OR REPLACE FUNCTION categoritzar_preu(preu NUMERIC)
RETURNS TEXT AS $$
BEGIN
IF preu < 10 THEN
RETURN 'Barat';
ELSIF preu BETWEEN 10 AND 50 THEN
RETURN 'Mitjà';
ELSE
RETURN 'Car';
END IF;
END;
$$ LANGUAGE plpgsql;Gestió d'errors en PL/pgSQL
PL/pgSQL permet capturar i gestionar errors utilitzant blocs EXCEPTION.
Exemple pràctic
CREATE OR REPLACE FUNCTION dividir(numerador NUMERIC, denominador NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN numerador / denominador;
EXCEPTION
WHEN division_by_zero THEN
RETURN NULL; -- Retorna NULL si es produeix una divisió per zero
END;
$$ LANGUAGE plpgsql;Altres llenguatges procedimentals
A més de PL/pgSQL, PostgreSQL suporta altres llenguatges procedimentals com PL/Python, PL/Perl, i PL/Tcl. Aquests llenguatges permeten escriure funcions en llenguatges de programació diferents, proporcionant més flexibilitat.
Exemple amb PL/Python
CREATE OR REPLACE FUNCTION sumar(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
return a + b
$$ LANGUAGE plpythonu;Exercicis pràctics
Exercici 1: Crear una funció per calcular l'IVA
Crea una funció anomenada calcular_iva que prengui un preu i un percentatge d'IVA com a paràmetres i retorni el preu amb l'IVA inclòs.
CREATE OR REPLACE FUNCTION calcular_iva(preu NUMERIC, iva NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
RETURN preu + (preu * iva / 100);
END;
$$ LANGUAGE plpgsql;Exercici 2: Crear una funció per categoritzar edats
Crea una funció anomenada categoritzar_edat que prengui una edat com a paràmetre i retorni una categoria (Infant, Adolescent, Adult, Sènior).
CREATE OR REPLACE FUNCTION categoritzar_edat(edat INTEGER)
RETURNS TEXT AS $$
BEGIN
IF edat < 13 THEN
RETURN 'Infant';
ELSIF edat BETWEEN 13 AND 19 THEN
RETURN 'Adolescent';
ELSIF edat BETWEEN 20 AND 64 THEN
RETURN 'Adult';
ELSE
RETURN 'Sènior';
END IF;
END;
$$ LANGUAGE plpgsql;Conclusió
En aquest tema, hem explorat PL/pgSQL, el llenguatge procedimental natiu de PostgreSQL, i hem vist com crear funcions, utilitzar estructures de control de flux i gestionar errors. També hem introduït altres llenguatges procedimentals com PL/Python. Amb aquests coneixements, pots començar a escriure funcions i procediments més complexos per a les teves aplicacions de bases de dades.
Curs de PostgreSQL
Mòdul 1: Introducció a PostgreSQL
Mòdul 2: Operacions bàsiques de SQL
Mòdul 3: Consultes SQL avançades
Mòdul 4: Disseny de bases de dades i normalització
Mòdul 5: Funcionalitats avançades de PostgreSQL
Mòdul 6: Optimització i millora del rendiment
- Optimització de consultes
- Estratègies d'indexació
- Analitzant el rendiment de les consultes
- Vacuuming i manteniment
Mòdul 7: Seguretat i gestió d'usuaris
- Rols d'usuari i permisos
- Mètodes d'autenticació
- Encriptació de dades
- Còpia de seguretat i restauració
Mòdul 8: Treballant amb JSON i funcionalitats NoSQL
Mòdul 9: Extensions i eines avançades
- PostGIS per a dades geoespacials
- Cerca de text complet
- Wrappers de dades externes
- PL/pgSQL i altres llenguatges procedimentals