Procedimientos Almacenados en PostgreSQL

PROCEDIMIENTOS ALMACENADOS -  FUNCIONES EN POSGREST

EJERCICIOS:

1. Con una función, dependiendo de un parámetro de entrada que podrá ser: CINCO o TODOS, listar de la tabla “categorias”  el respectivo número de registros.

create or replace function listarCategorias(opcion varchar,out c1 int, out c2 varchar) returns setof record as $$ begin

if opcion='cinco' then

return query select * from categorias limit 5; end if;

if opcion='todos' then

return query select * from categorias; end if; end;

$$ language plpgsql

select * from listarCategorias('todos')

2.                   Crear una función para insertar una categoría, enviando como parámetro el nombre de la categoría, y finalmente mostrar el registro reciente insertado.

create or replace function insertarMostrar(nombre varchar,out c1 int, out c2 varchar) returns setof RECORD as $$ begin

insert into categorias(cat_nombre) values(nombre);

return query select * from categorias order by cat_id desc limit 1; end;

$$ language plpgsql

select*from insertarMostrar('SALCHICHONES')

3.                   Crear un procedimiento almacenado que lea 3 parámetros, el primero será para identificar el caso, el segundo es un parámetro del id y el tercero es la descripción para la tabla prueba, si el número ingresado en el primer parámetro es 1, se deberá realizar una inserción en la tabla pruebas con los parámetros 2 y 3. si el primer parámetro es 2 se debe realizar una actualización del registro correspondiente al id enviado en el parámetro 2 y se debe actualizar la descripción del parámetro 3. Y finalmente si el parámetro 1 es igual a 3, se debe eliminar el registro correspondiente al id enviado en el parámetro 2. 

CREATE TABLE prueba

(

idprueba integer, descripcion varchar(40),

PRIMARY KEY(idprueba) )

create or replace function abm_prueba(band numeric, cidprueba integer, cdescripcion varchar) returns void as $$

begin

case band

                                when 1 then

                                                INSERT INTO prueba VALUES (cidprueba, cdescripcion);

                                when 2 then

                                                update prueba set descripcion=cdescripcion where idprueba=cidprueba;                  when 3 then

                                                delete from prueba where idprueba=cidprueba; end case; end

$$ language plpgsql;

select abm_prueba(3,4,'pruebamodificado'); select * from prueba

Ejercicio en Clase - Marzo 26 de 2020 - Funciones

create or replace function fn_prod() returns int as $$
declare
x record;
prom decimal;
conta int;
begin
conta=0;
prom=(select avg(prod_precio) from productos);
for x in select * from productos loop
if x.prod_precio < prom then
update productos set prod_precio=prod_precio+2000 where prod_id=x.prod_id;
conta=conta+1;
end if;
end loop;
return conta;
end;
$$ language plpgsql
select fn_prod();

create table productos (
prod_id int,
prod_nombre varchar,
prod_precio decimal
);

insert into productos (prod_id, prod_nombre,prod_precio)
values (10, 'Alcohol', 2500);

select *from productos;

Taller de Funciones en PostgreSQL

PostgreSQL - Manual de Instalación

Apreciados estudiantes, favor tener en cuenta el manual de instalación en sus casas de la herrameinta PostgreSQL, como lo tratamos en la clase de hoy jueves 19 de marzo de 2020 a las 7:30 am.

Una vez instalado, crear una base de datos y realizar las siguientes funciones (realizar las tablas respectivas de acuerdo a la función):

--Recorrer la tabla productos y mostrar los nombres.

CREATE or replace FUNCTION pro_funcion() RETURNS setof varchar AS $$

DECLARE

i record;

BEGIN

FOR i IN SELECT * FROM productos LOOP

                return next i.prod_nombre || '---' || i.prod_precio;

END LOOP;

END;

$$ LANGUAGE plpgsql;

select * from pro_funcion()

 ________________________________________________

--Buscar en la tabla productos por el nombre enviando un parámetro

CREATE OR REPLACE FUNCTION prod_funcion2(nom varchar) RETURNS SETOF varchar AS $$ BEGIN

    RETURN QUERY SELECT prod_nombre FROM productos WHERE prod_nombre LIKE nom || '%'; END;

$$ LANGUAGE plpgsql;

SELECT prod_funcion2('C');

DROP FUNCTION prod_funcion2(nom TEXT)

 ____________________________________________

--crear la tabla

CREATE or replace FUNCTION nuevosdatos() RETURNS TABLE(NOMBRE varchar, TOTAL real, FECHA timestamp) AS $$

DECLARE

i record;

BEGIN

drop table med;

create table med(id integer,nombre varchar,total real,fecha timestamp);

FOR i IN SELECT * FROM medicamentos LOOP

insert into med values(i.codigo,i.nombre,i.precio*i.cantidad,current_timestamp);

END LOOP;

return query select med.nombre,med.total,med.fecha from med;

END;

$$ LANGUAGE plpgsql;

--getpgusername()

--current_user

select * from nuevosdatos()

--ejemplo de asignar una consulta a una variable

create or replace function fn_categoria(id int) returns varchar as $$

declare

x varchar;

begin

x=(select cat_nombre from categorias where cat_id=id);

return x;

end;

$$ language plpgsql

select fn_categoria(5);

create or replace function fn_prod() returns int as $$

declare

x record;

prom decimal;

conta int;

begin

conta=0;

prom=(select avg(prod_precio) from productos);

for x in select * from productos loop

if x.prod_precio < prom then

update productos set prod_precio=prod_precio+2000 where prod_id=x.prod_id;

conta=conta+1;

end if;

end loop;

return conta;

end;

$$ language plpgsql

select fn_prod();

Creación de Usuarios y Asignación de Privilegios

CREACION DE USUARIOS Y ASIGNACION DE PRIVILEGIOS.

1.    Ingresar al PG Admin IV.

2.    Borrar todas las bases de datos, excepto la de postgres.

3.    Utilice la ventana de comandos SQL y use CREATE DATABASE, para crear una nueva base de datos llamada empresa_apellido (Ejemplo: empresa_anaya).

4.    En la nueva BD Importe el archivo “empresa.sql” y ejecútelo para crear la estructura de las tablas e insertar los datos, verifique la creación de las tablas en el árbol de objetos.

5.    Crear un usuario llamado “nomina" con password ‘e123’ y que tenga permisos de LOGIN.

6.    Para observar todos los usuarios de postgres use: SELECT * FROM pg_user;

7.    Ahora se removerán todos los permisos para todos los usuarios para la nueva BD.

8.    Usar la siguiente Sentencia SQL: REVOKE ALL ON DATABASE empresa_apellido FROM public;

9.    Desconéctese del servidor de postgres. Ubique en el árbol de objetos el nombre del servidor (PostgreSQL 9.1(localhost:5432)) clic derecho ->Disconnect.

10. Click derecho sobre el nombre del servidor -> propiedades, aparecerá una ventana emergente con la configuración de la conexión, cambie la propiedad username por nomina y la propiedad Maintenance DB por empresa_apellido (esto quiere decir que ahora no ingresaremos con el usuario postgres sino con el nuevo usuario llamado nomina y nos conectaremos a la nueva BD). Conéctese de nuevo ingresando el password de ese usuario. ¿analizar qué sucede?

11. Vuelva a modificar los parámetros de conexión username: postgres y Maintenance DB: postgres, conéctese y abra la ventana de comandos SQL para otorgar privilegios de conexión al usuario nomina sobre la Base de datos empresa_apellido, busque la sintaxis en los ejemplos de la teoría del GRANT, impleméntela y ejecútela.

12. Volver a realizar el punto 8. La conexión ahora si debe ser exitosa, abra la ventana de comandos y ejecute la siguiente consulta SQL: SELECT * FROM categorias; ¿analizar qué sucede?, observar en el árbol de objetos las demás tablas de esa base de datos.

13. Conectarse de nuevo con el usuario postgres y a la BD postgres para ahora darle privilegios (GRANT) de SELECT e INSERT sobre la tabla categorias para el usuario nomina.  Arme la consulta SQL DCL (Lenguaje de Control de Datos) para satisfacer esa petición. Ahora ingrese de nuevo con el usuario nomina y conectese a la BD empresa_apellido para ejecutar las siguientes instrucciones:

SELECT * FROM categorias;

INSERT INTO categorias VALUES (9,‘CATEGORIA DE PRUEBA’);

Las instrucciones deben ejecutarse con éxito. Verifique que se insertóc orrectamente.

14. Intente eliminar el último registro insertado con la siguiente instrucción SQL DML

DELETE FROM categorias WHERE cat_nom = ‘CATEGORIA DE PRUEBA’;

Lenguaje de Definición de Datos

DDL - DML - Bases de Datos

DDL y DML

Tabla de empleados (TEMPLE1) y departamentos (TDEPAR2)

Postgresql - Tutorial

Comunicado UTS

Buen día.

1. Los docentes cátedra imparten de manera remota o virtual sus clases.
2. Buscar los medios tecnológicos para impartir las clases. El día jueves en la tarde recibo evidencias al correo de lo laborado lunes, martes y miércoles. El día domingo recibo el informe de lo laborado jueves, viernes y sábado.
3. Les agradezco la seriedad a este suceso que es mundial y no nos compliquemos en la forma sino que pensemos que esto es mediático. No estamos en vacaciones.
4. Los estudiantes deben estar en clase y desarrollando actividades esta semana.
5. Los parciales que estaban pendientes para esta semana se deben reprogramar. No sacar notas esta semana.
6. Cualquier otra novedad les estaré comunicando.
Mil gracias por su comprensión. Uds ahora son quienes más deben apoyar esta situación.
Dios nos bendiga y a sus familias.