PLPGSQL: Stored procedures

• 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

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

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

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