
RETURNS void
LANGUAGE 'plpgsql';
AS $BODY$
DECLARE
rec record;
contador integer :=0;
BEGIN
--recorre tabla pasajero y lo guarda en la variable rec
FOR rec IN SELECT * FROM pasajero LOOP
RAISE NOTICE 'id: % ,Nombre: % ',
rec.id,rec.nombre;
contador := contador + 1;
END LOOP;
RAISE NOTICE '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
--FUNCION QUE RETORNA UNA TABLA
--Mostrar tabla con plpgsql
--https://stackoverflow.com/questions/18084936/pl-pgsql-functions-how-to-return-a-normal-table-with-multiple-columns-using-an
DROP FUNCTION consulta_t_pasajero(p_pasajero_id integer);
CREATE OR REPLACE FUNCTION consulta_t_pasajero(p_pasajero_id integer)
RETURNS TABLE(id integer, nombre character varying, direccion_residencia character varying, fecha_nacimiento date)
LANGUAGE plpgsql
AS $BODY$
BEGIN
IF p_pasajero_id IS NULL THEN
RETURN QUERY
SELECT pasajero.id, pasajero.nombre, pasajero.direccion_residencia, pasajero.fecha_nacimiento
FROM public.pasajero;
END IF;
RETURN QUERY
SELECT pasajero.id, pasajero.nombre, pasajero.direccion_residencia, pasajero.fecha_nacimiento
FROM public.pasajero
WHERE pasajero.id = p_pasajero_id;
END;
$BODY$
--Retorno en forma de fila
SELECT consulta_t_pasajero(NULL);
SELECT consulta_t_pasajero(50);
--Retorno en forma de tabla
SELECT * FROM consulta_t_pasajero(NULL);
SELECT * FROM consulta_t_pasajero(50);
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.
.
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.
.
.
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:
.
.
for var in valores:
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.
.
.
.
Aplícale estructuras de control a tu base de datos:
if statments
, case statements, loops statements, while loops, exit y continue..
.
Este es un tutorial de práctica con una base de datos donde ensayas las partes básicas de la creación de funciones.
Buenas Tardes.
Esta clase debería ser más compleja y con una introducción previa donde haya mayor explicación.
Este curso está muy bien estructurado, explicado de forma clara y concisa gracias Oswaldo.
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.
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 $$
DECLARE
rec record; // Record almacena datos.
counter integer := 0; // Entero
BEGIN
FOR rec IN SELECT * FROM passenger LOOP // rec almacenará todos los datos de la tabla passenger
RAISE NOTICE 'Un pasajero se llama %', rec.name; // El % será reemplazado por la variable que vamos a mostrar. Para acceder al nombre usamos rec.name
counter := counter + 1;
END LOOP;
RAISE NOTICE '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.
CREATE FUNCTION testPL()
RETURNS void // No regresa nada.
AS $$
DECLARE
rec record;
counter integer := 0;
BEGIN
FOR rec IN SELECT * FROM passenger LOOP
RAISE NOTICE 'Un pasajero se llama %', rec.name;
counter := counter + 1;
END LOOP;
RAISE NOTICE 'La cantidad de pasajeros es: %', counter;
END
$$
LANGUAGE PLPGSQL;
SELECT testPL();
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.
DROP FUNCTION testPL(); // Borramos la función anterior, para poder actualizar el tipo de dato que retorna
CREATE OR REPLACE FUNCTION testPL()
RETURNS integer
AS $$
DECLARE
rec record;
counter integer := 0;
BEGIN
FOR rec IN SELECT * FROM passenger LOOP
RAISE NOTICE 'Un pasajero se llama %', rec.name;
counter := counter + 1;
END LOOP;
RAISE NOTICE 'La cantidad de pasajeros es: %', counter;
RETURN counter;
END
$$
LANGUAGE PLPGSQL;
SELECT testPL(); // 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:
DECLARE
rec record;
counter integer := 0;
BEGIN
FOR rec IN SELECT * FROM passenger LOOP
RAISE NOTICE 'Un pasajero se llama %', rec.name;
counter := counter + 1;
END LOOP;
RAISE NOTICE 'La cantidad de pasajeros es: %', counter;
RETURN counter;
END
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.
Considero que en los siguientes vídeos explican un poco mejor este concepto de PL/PgSQL:
Espero te pueda servir.
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>
Me voy a saltar esta clase porque no entiendo nada…
PL
Para retornar el área de un triangulo (useless pues no tiene relación con la db, pero sirve para ir practicando)
CREATE OR REPLACE FUNCTION triangle_area(base float, hight float)
RETURNS real
AS
$BODY$
DECLARE
area float;
half float := 0.5;
BEGIN
area := base*hight*half;
RAISE NOTICE 'The area is: %', area;
RETURN area;
END;
$BODY$
LANGUAGE 'plpgsql';
SELECT * FROM triangle_area(7,5);
Cual es la diferencia entre las functions y los procedures ?
Información resumida de esta clase
#EstudiantesDePlatzi
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.
DO $$
DECLARE
rec record
BEGIN
FOR rec IN SELECT * FROM pasajero LOOP
RAISE NOTICE 'Un pasajero se llama %', rec.nombre;
END LOOP;
END
$$;
Creo que esta clase se pudo haber partido en dos
Ya casi DataTreeGardener 😛
Interesante tema PL/SQL, pero requiere que los estudiantes practiquen mas para obtener la habilidad y claro los querys a realizar.
OK… Gracias por la clase. Tengo una curiosidad. En esta clase se hablo en todo momento de crear un PL “Procedimiento Almacenado” pero en realidad lo que se creo fue una funcion y esta a su vez se guardo en el grupo de Functions y no en Procedures como yo lo esperba. En resumidas cuentas, cual es la diferencia entre el Functions y Procedures? Cuando puedo usar uno u la otra?
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
CREATE OR REPLACE FUNCTION obtener_nombres(edad integer)
RETURNS SETOF text AS $$
DECLARE
nombre text;
BEGIN
FOR nombre IN SELECT nombre FROM clientes WHERE edad = edad LOOP
RETURN NEXT nombre;
END LOOP;
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 * FROM obtener_nombres(30);
El resultado sería un conjunto de nombres de clientes con edad 30.
PL con python
no me gusta este lenguaje , prefiero python con la libreria
Ejemplos sencillos de los distintos procedural languages que soporta postgresql (sql, c , plpgsql, python, perl y tcl)
https://pgdash.io/blog/postgres-server-side-programming.html#:~:text=PL%2FpgSQL Functions&text=Four such languages are supported,mod_python from the Apache era.
Me funciono así
DO $$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM pasajero LOOP
RAISE NOTICE 'un pasajero se llama %', rec.nombre;
END LOOP;
END
$$;
Con la publicacion de un compañero la replique y cambien la condicion if, adicionando un mensaje en caso de no ingresar id, ademas de agregar un sino en la consulta.
<create or replace function consulta_pasajero2(pasajero_id integer)
returns table (id integer, nombre character varying, direccion_residencia character varying, fecha_nacimiento date)
AS $$
begin
if pasajero_id is null then
RAISE NOTICE 'Ingrese por favor un Id de pasajero para consultar';
else
return query
select pasajero.id, pasajero.nombre, pasajero.direccion_residencia, pasajero.fecha_nacimiento from pasajero where pasajero.id=pasajero_id;
end if;
end;
$$
LANGUAGE PLPGSQL;>
Este curso me encantó. Perfectamente explicado y aplicado.
Entonces el RAISE NOTICE es un símil al console.log y PLPGSQL es como un lenguaje de programación
se puede cambiar el nombre de la columna al obtener el resultado de la función con el código:
select importantePL() as resultado_funcion;
DROP FUNCTION importantepl()
CREATE OR REPLACE FUNCTION importantePL()
RETURNS integer
AS $$
DECLARE
rec record;
contador integer:=0;
BEGIN
FOR rec IN SELECT * from pasajero LOOP
RAISE NOTICE ‘Un pasajero se llama %’, rec.nombre;
contador:= contador +1;
END LOOP;
RAISE NOTICE ‘Conteo es %’, contador;
RETURN contador;
END
$$
LANGUAGE PLPGSQL;
SELECT importantePL()
Mi aporte
-- Mustra las ordenes de x cliente por su cedula
DO
$$
<<q_client>>
DECLARE
_cedula clients.cedula%type := '12882993';
r_client clients%rowtype;
BEGIN
-- buscar al cliente
SELECT *
INTO r_client
FROM clients WHERE cedula = _cedula;
IF (NOT FOUND) THEN
RAISE NOTICE 'El cliente % no esta registrado', _cedula;
RETURN;
ELSE
RAISE NOTICE 'Datos del cliente';
RAISE NOTICE 'Cedula : %', _cedula;
RAISE NOTICE 'Nombre : % %', r_client.name, r_client.last_name;
END IF;
DECLARE
r_order orders%rowtype;
counter INTEGER := 0;
BEGIN
FOR r_order IN (SELECT * FROM orders WHERE client_id = q_client.r_client.client_id) LOOP
RAISE NOTICE 'Order Codigo #% Emitida: % Estado: %', r_order.code, r_order.created_at::DATE, r_order.state;
counter := counter +1;
END LOOP;
IF (counter = 0) THEN
RAISE NOTICE 'El cliente % No ha realizado ninguna orden', q_client.r_client.cedula;
END IF;
END;
END q_client;
$$ LANGUAGE PLpgSQL;```
🌿DataTreeGardener
PL Procedural language, también conocido como procedimientos almacenados, estas nos ayuda a desarrollar código directamente en el motor de bases de datos.
Me encantó esta clase, siento que entre a algo más complejo, pero realmente útil.
Mi cerebro salió del grupo.
me toco borrarla primero y luego crearla por que molestaba por el tipo de dato retorno
-- Primero, elimina la función existente (¡cuidado, esto borrará la función y sus definiciones!)
DROP FUNCTION IF EXISTS importantePL();
-- Luego, crea la función con el nuevo tipo de retorno
CREATE OR REPLACE FUNCTION importantePL()
RETURNS integer
AS $$
DECLARE
rec record;
contador integer := 0;
BEGIN
FOR rec IN SELECT * FROM pasajero LOOP
RAISE NOTICE 'un pasajero se llama %', rec.nombre;
contador := contador + 1;
END LOOP;
RAISE NOTICE 'conteo es %', contador;
RETURN contador;
END
$$
LANGUAGE PLPGSQL;
comienza con funciones
Lo mejor SQL
Esta clase lo ah explicado muy bien a pesar que es muy delicado este tipo de temas.
En caso que se quieran agregar mas variables intercaladas en el texto :
DO $$
DECLARE rec record;
BEGIN
FOR rec IN SELECT * FROM pasajeros LOOP
RAISE NOTICE 'el pasajero: % con id % nacio el : %', rec.nombre, rec.id, rec.fecha_nacimiento;
END LOOP;
END
$$
Muy buena la clase y claros los ejemplos
My fuction BIN:
CREATE FUNCTION BIN()
RETURNS INTEGER
AS $$
DECLARE
bin record;
contador integer := 0;
BEGIN
FOR bin IN SELECT * FROM pasajero LOOP
RAISE NOTICE 'El nombre del pasajero es %', bin.nombre;
RAISE NOTICE 'y su fecha de nacimiento es %', bin.fecha_nacimiento;
contador := contador + 0.5;
END LOOP;
RAISE NOTICE 'El conteo es %', contador;
RETURN contador;
END
$$
LANGUAGE PLPGSQL;
se pudo dar la clase mas detallada y repartida en mas clases ya que es información muy valiosa para solo 1 clase
woooo aprendiendo día a día estupendo
La notacion de := me hizo acordar a Pascal
Puedes imprimir mas de una variable
DO $$
DECLARE
rec record;
BEGIN
FOR rec IN SELECT * FROM trenes LOOP
RAISE NOTICE 'El modelo % tiene capacidad de % Kilos', rec.modelo, rec.capacidad;
END LOOP;
END
$$
Es PL/pgSQL, no PL/SQL que es la implementación de SQL de Oracle, para el motor de base de datos del mismo nombre.
Listo, cada paso es como un pequeño reto.
Creando PL SQL
No: Los Procedimientos Almacenados son los “Stored Procedures”. PL/PgSQL es la implementación de SQL de PostgreSQL.
pgAdmin se ve bastante completo. Es maravilloso.
También se pude usar al inicio de nuestra función:
drop function IF exists importantePL();
para que la borre por default al iniciar el código si es que la función este e inmediatamente la crea de nuevo.
Es decir con el PL podemos realizar consultas avanzadas usando funciones.
PostgreSQL puede con algunas librerías ejecutar código escrito en Python o C. 🚀
Wau es excelente realmente este curso.
Estructura de un Pl es: Decalració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 o bitácora de postgres
1- DECLARE, es la sentencia para la declaración de variables. 2- El tipo de datos que nos sirve para almacenar una fila es record. 3- llos siguientes caracteres := símolizan asignación
Es importante al declarar una función decirle al lenguaje que en que lenguaje está escrito la función ya que postsgre con ciertas librerías soporta python, c++ y pl entre otros.
"""PL/SQL Usando codigo"""
DROP FUNCTION importantepl() ;
CREATE OR REPLACE FUNCTION importantePL() RETURNS integer AS $$
DECLARE
rec record;
contador integer :=0;
BEGIN
FOR rec IN SELECT * FROM pasajero LOOP
RAISE NOTICE 'Un pasajero se llama %', rec.nombre;
contador = contador + 1;
END LOOP;
RAISE NOTICE 'Conteo es %', contador;
RETURN contador;
END
$$
LANGUAGE PLPGSQL;
SELECT importantePL();
Las pl su objetivo principal es realizar consultas a las bases de datos una pl que no tenga que ver con consultas a la base de datos no es muy útil.
/*Mostrar una tabla o un solo registro con plpg*/
DROP FUNCTION consulta_t_pasajero(p_pasajero_id integer);
CREATE OR REPLACE FUNCTION consulta_t_pasajero2(p_pasajero_id integer)
RETURNS TABLE(id integer,
nombre character varying,
direccion_residencia character varying,
fecha_nacimiento date)
LANGUAGE plpgsql
AS $BODY$
BEGIN
IF p_pasajero_id IS NULL THEN
RETURN QUERY
SELECT pasajero.id, pasajero.nombre,
pasajero.direccion_residencia,
pasajero.fecha_nacimiento
FROM public.pasajero;
END IF;
RETURN QUERY
SELECT pasajero.id, pasajero.nombre,
pasajero.direccion_residencia,
pasajero.fecha_nacimiento
FROM public.pasajero
WHERE pasajero.id = p_pasajero_id;
END;
$BODY$
--Retorno en forma de fila
SELECT consulta_t_pasajero(NULL);
SELECT consulta_t_pasajero(50);
--Retorno en forma de tabla
SELECT * FROM consulta_t_pasajero(NULL);
SELECT * FROM consulta_t_pasajero(50);
Muy interesante el poder poner código de diversos lenguajes.
#DataTreeGardener
Interesante clase
CREATE OR REPLACE FUNCTION importantePL()
RETURNS integer
AS $$
DECLARE
rec record; -- := para asignación de variable, record tipo de dato
contador integer := 0;
BEGIN
FOR rec IN SELECT id, COALESCE(nombre, 'Sin nombre') as nombre FROM pasajero LOOP -- bucle FOR IN LOOP
RAISE NOTICE 'Un pasajero de ID % se llama %', rec.id, rec.nombre; -- % comodin para concatenar el valor de variable
contador := contador + 1;
END LOOP;
RAISE NOTICE 'Conteo es %', contador;
RETURN contador;
END
$$
LANGUAGE PLPGSQL;
Explicación sencilla y clara
Muy muy interesante la creación de funciones en lenguaje PL/SQL! Ansioso por ver la parte de triggers, me suena a una interrupción en un microcontrolador.
Genial 😄
Esto es lo mejor jamas pense que se pudiera hacer esto en una base de datos
PL/PgSQL: Lenguaje de PostgreSQL para Queries
DO: Para ejecutar bloques de código.
Utilizamos := para asignar variables
<CREATE OR REPLACE FUNCTION importantePL()
RETURNS integer
AS $$
DECLARE
rec RECORD;
contador INTEGER := 0;
BEGIN
FOR rec IN SELECT * FROM pasajeros LOOP
RAISE NOTICE 'Un pasajero se llama %', rec.nombre;
contador := contador + 1;
END LOOP;
RAISE NOTICE 'Conteo es %', contador;
RETURN contador;
END
$$
LANGUAGE plpgsql;>
Estructura básica PL
Estructura básica PL
Bucle for en PostgreSQL
RAISE NOTICE: Para mostrar en pantalla el resultado, es como un print en python.
CREATE FUNCTION: Para encapsular un bloque de código dentro de una función
Crear una PL utilizando Functions de pgAdmin
Ejemplo: contador de pasajeros
create function contador_pasajeros()
returns integer
as $$
declare
viajero record;
counter integer := 0;
begin
for viajero in select id from pasajero
loop
counter := counter + 1;
end loop;
return counter;
end $$
language plpgsql;
select contador_pasajeros();
Se pueden utilizar los porcentajes para poner varios valores como en un lenguaje de programación
$$
DECLARE
rec record;
contador integer := 0;
BEGIN
FOR rec IN SELECT * from pasajeros LOOP
contador := contador + 1;
RAISE NOTICE 'El pasajero % se llama %', contador, rec.nombre;
END LOOP;
RETURN CONTADOR;
END
$$
Una sentencia PL/SQL siempre deberá tener una consulta en su ejecución, de lo contrario no tiene mucho valor.
AS $$
DECLARE
rec record;
contador integer := 0;
BEGIN
FOR rec IN SELECT * FROM pasajero LOOP
RAISE NOTICE ‘El pasajero %’, rec.nombre;
contador := contador + 1;
RAISE NOTICE ‘Conteo es %’ , contador;
END LOOP;
– RAISE NOTICE ‘Conteo es %’ , contador;
RETURN contador;
END
$$
me saque la duda de xq yo tenia que poner las comillas a los nombres de mis tablas jaja
Esto es la razón por la que estoy aquí. Me siento emocionada y nerviosa.
cambio con un return integer
inicio del pl
¿Quieres ver más aportes, preguntas y respuestas de la comunidad?
o inicia sesión.