No tienes acceso a esta clase

隆Contin煤a aprendiendo! 脷nete y comienza a potenciar tu carrera

Curso de PostgreSQL

Curso de PostgreSQL

Oswaldo Rodr铆guez Gonz谩lez

Oswaldo Rodr铆guez Gonz谩lez

PL/SQL

22/32
Recursos

Aportes 68

Preguntas 13

Ordenar por:

Los aportes, preguntas y respuestas son vitales para aprender en comunidad. Reg铆strate o inicia sesi贸n para participar.

![](

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.

  • 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 鈥榤essage鈥, 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 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 
$$

CREATE FUNTION - Declaraci贸n de una funci贸n SQL

CREATE FUNCTION  consulta_usuarios() 
    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);

Este curso est谩 muy bien estructurado, explicado de forma clara y concisa gracias Oswaldo.

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 鈥楢lgo 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.

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.

Ya casi DataTreeGardener 馃槢

Buenas Tardes.
Esta clase deber铆a ser m谩s compleja y con una introducci贸n previa donde haya mayor explicaci贸n.

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

Me voy a saltar esta clase porque no entiendo nada鈥

OK鈥 Gracias por la clase. Tengo una curiosidad. En esta clase se hablo en todo momento de crear un PL 鈥淧rocedimiento 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?

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;```
DO $$
DECLARE
	rec record
BEGIN
	FOR rec IN SELECT * FROM pasajero LOOP
		RAISE NOTICE 'Un pasajero se llama %', rec.nombre;
	END LOOP;
END
$$;

馃尶DataTreeGardener

Cual es la diferencia entre las functions y los procedures ?

PL Procedural language, tambi茅n conocido como procedimientos almacenados, estas nos ayuda a desarrollar c贸digo directamente en el motor de bases de datos.

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.

Listo, cada paso es como un peque帽o reto.

Creando PL SQL

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>

No: Los Procedimientos Almacenados son los 鈥淪tored Procedures鈥. PL/PgSQL es la implementaci贸n de SQL de PostgreSQL.

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.

pgAdmin se ve bastante completo. Es maravilloso.

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;

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. 馃殌

Este curso me encant贸. Perfectamente explicado y aplicado.

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 鈥楿n pasajero se llama %鈥, rec.nombre;
contador:= contador +1;
END LOOP;
RAISE NOTICE 鈥楥onteo es %鈥, contador;
RETURN contador;
END
$$
LANGUAGE PLPGSQL;

SELECT importantePL()

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 鈥楨l pasajero %鈥, rec.nombre;
contador := contador + 1;
RAISE NOTICE 鈥楥onteo es %鈥 , contador;
END LOOP;
鈥 RAISE NOTICE 鈥楥onteo es %鈥 , contador;
RETURN contador;
END
$$

me saque la duda de xq yo tenia que poner las comillas a los nombres de mis tablas jaja

Creo que esta clase se pudo haber partido en dos

Esto es la raz贸n por la que estoy aqu铆. Me siento emocionada y nerviosa.

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:

.

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 鈥榲alores鈥 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.

cambio con un return integer

inicio del pl