Funciones y Consultas en PostgresSQL
Funciones y Consultas en PostgresSQL
Se puede acceder a más de una tabla en una función. En el siguiente ejemplo realizaremos una función que acceda a la tabla ‘Usuarios’:
- Crearemos la tabla usuarios con los siguientes atributos:
Create table usuarios(
nombre varchar (30),
clave varchar(10)
);
- Ahora crearemos una función que retorne la clave de un usuario que se pasa por parámetro:
CREATE OR REPLACE FUNCTION devuelveclave(varchar) RETURNS varchar AS
‘select clave from usuarios where nombre=$1;’
language sql;
- Insertamos datos a la tabla ‘Usuarios’.
insert into usuarios (nombre, clave) values (‘Susana’,’River’);
- Luego probamos la función haciendo la llamada a ‘devuelveclave’ de la siguiente manera:
select retornarclave(‘Susana’);
Ejercicio:
- Crear una tabla con la siguiente estructura:
create table medicamentos(
codigo serial,
nombre varchar(20),
laboratorio varchar(20),
precio decimal(5,2),
cantidad smallint,
primary key(codigo)
);
- Insertar los siguientes valores :
insert into medicamentos (nombre,laboratorio,precio,cantidad)values(‘Sertal’,’Roche’,5.2,100);
insert into medicamentos
(nombre,laboratorio,precio,cantidad)values(‘Buscapina’,’Roche’,4.10,200);
insert into medicamentos
(nombre,laboratorio,precio,cantidad) values(‘Amoxidal 500′,’Bayer’,15.60,100);
insert into medicamentos
(nombre,laboratorio,precio,cantidad) values(‘Paracetamol 500′,’Bago’,1.90,200);
insert into medicamentos
(nombre,laboratorio,precio,cantidad) values(‘Bayaspirina’,’Bayer’,2.10,150);
insert into medicamentos
(nombre,laboratorio,precio,cantidad) values(‘Amoxidal jarabe’,’Bayer’,5.10,250);
- Crear una función que retorne el precio promedio de la tabla medicamentos.
- Imprimir el precio promedio de los medicamentos.
- Imprimir los medicamentos que tienen un precio mayor al promedio.
Función SQL que no Retorna Datos
Cuando queremos crear una función que no retorne dato lo debemos indicar luego de la palabra clave returns disponiendo el valor VOID. La sintaxis sería la siguiente:
| CREATE OR REPLACE FUNCTION nombre_funcion(lista_parametros)
RETURNS VOID AS comandos_sql language sql; |
Veamos el siguiente ejemplo para entender un poco más las funciones que no retornan datos:
- Crearemos una función que inserte dos registros en la tabla ‘Usuarios‘ creada en uno de los ejercicios anteriores:
CREATE OR REPLACE FUNCTION cargarusuarios() RETURNS VOID
AS
‘
insert into usuarios (nombre, clave) values (”Marcelo”,”Boca”);
insert into usuarios (nombre, clave) values (”JuanPerez”,”Juancito”);
insert into usuarios (nombre, clave) values (”Susana”,”River”);
insert into usuarios (nombre, clave) values (”Luis”,”River”);
‘
language sql;
- Luego para llamar la función haremos lo siguiente:
select cargarusuarios();
Ejercicio:
- Usar la información contenida en la tabla ‘Medicamentos ‘ y realizar lo siguiente:
- Implementar una función que reciba el código de un medicamento y lo borre.
- Llamar a la función.
- Mostrar la tabla de medicamentos y verificar que se haya borrado el registro.
Función SQL que Retorna un Dato Compuesto
Ya vimos cómo una función puede no retornar dato y cómo retornar un dato simple (integer, varchar etc.), ahora veremos como retornar toda una fila de una tabla.
Para indicar que una función retorna una fila de una tabla debemos indicar luego de la palabra returns el nombre de la tabla. Con el siguiente ejemplo entenderemos un poco mejor cómo devolver la información de un registro completo:
- En la tabla ‘Usuarios’ insertar los siguientes registros:
insert into usuarios (nombre, clave) values (‘Marcelo’,’Boca’);
insert into usuarios (nombre, clave) values (‘JuanPerez’,’Juancito’);
insert into usuarios (nombre, clave) values (‘Susana’,’River’);
insert into usuarios (nombre, clave) values (‘Luis’,’River’);
- Luego definimos una función donde se indique el nombre de la tabla como dato a devolver de la siguiente manera:
CREATE OR REPLACE FUNCTION retornausuario(varchar) RETURNS usuarios
AS
‘select * from usuarios where nombre=$1;’
LANGUAGE sql;
- Una vez que hagamos la llamada a la función obtendremos como resultado todos los campos del usuario consultado:
(Luis,River)
Ejercicio:
- Usando la tabla de ‘Medicamentos’ realizar lo siguiente:
- Implementar una función que retorne el registro completo del medicamento más caro.
- Llamar a la función y ver el resultado.
CREATE TABLE department(
id integer PRIMARY KEY,
name text);
CREATE TABLE employee(
id integer primary key,
name text,
salary integer,
departmentid integer REFERENCES department);
INSERT INTO department VALUES (1, ‘Management’);
INSERT INTO department VALUES (2, ‘IT’);
INSERT INTO employee VALUES (1, ‘John Smith’, 30000, 1);
INSERT INTO employee VALUES (2, ‘Jane Doe’, 50000, 1);
insert INTO employee VALUES (3, ‘Fairlie Reese’, 63000, 1);
insert INTO employee VALUES (4, ‘Jack Jackson’, 60000, 2);
insert INTO employee values (5, ‘Harold Bibsom’, 40000, 2);
insert INTO employee VALUES (6, ‘Julio Garcia’, 70000, 2);
insert INTO employee VALUES (7, ‘Bernice Johnson’, 55000, 2);
insert INTO employee values (8, ‘Lily Leong’, 67000, 2);
insert INTO employee VALUES (9, ‘Abby Wood’, 57000, 2);
insert INTO employee VALUES (10, ‘Jeff Jeffries’, 52000, 2);
insert INTO employee VALUES (11, ‘Geordie O”Hare’, 42000, 2);
CREATE or REPLACE FUNCTION getemployee () RETURNS SETOF text AS ‘
DECLARE
myrow RECORD;
retval text;
BEGIN
FOR myrow IN SELECT * FROM employee LOOP
RETURN NEXT myrow.name;
END LOOP;
RETURN;
END;
‘ LANGUAGE ‘plpgsql’;
CREATE OR REPLACE FUNCTION getemployeedid (integer)
RETURNS SETOF integer AS ‘
DECLARE
myrow RECORD;
retval integer;
BEGIN
FOR myrow IN SELECT * FROM employee WHERE salary >= $1 LOOP
RETURN NEXT myrow.departmentid;
END LOOP;
RETURN;
END;
‘ LANGUAGE ‘plpgsql’;
CREATE TYPE deptavgs AS ( minsal integer, maxsal integer, avgsalary int8);
CREATE OR REPLACE FUNCTION avgdept() RETURNS deptavgs AS
‘
DECLARE
r deptavgs%ROWTYPE;
dept RECORD;
bucket int8;
counter integer;
BEGIN
bucket := 0;
counter := 0;
r.maxsal := 0;
r.minsal := 0;
FOR dept IN SELECT sum(salary) AS salary, d.id AS department
FROM employee e, department d WHERE e.departmentid = d.id
GROUP BY department LOOP
counter := counter + 1;
bucket := bucket + dept.salary;
IF r.maxsal <= dept.salary OR r.maxsal = 0 THEN
r.maxsal := dept.salary;
END IF;
IF r.minsal >= dept.salary OR r.minsal = 0 THEN
r.minsal := dept.salary;
END IF;
END LOOP;
r.avgsalary := bucket/counter;
RETURN r;
END
‘ language ‘plpgsql’;
CREATE TYPE salavgs AS
(deptid integer, minsal integer, maxsal integer, avgsalary int8);
CREATE OR REPLACE FUNCTION avgsal() RETURNS SETOF salavgs AS
‘
DECLARE
s salavgs%ROWTYPE;
salrec RECORD;
bucket int8;
counter int;
BEGIN
bucket :=0;
counter :=0;
s.maxsal :=0;
s.minsal :=0;
s.deptid :=0;
FOR salrec IN SELECT salary AS salary, d.id AS department
FROM employee e, department d WHERE e.departmentid = d.id
ORDER BY d.id LOOP
IF s.deptid = 0 THEN
s.deptid := salrec.department;
s.minsal := salrec.salary;
s.maxsal := salrec.salary;
counter := counter + 1;
bucket := bucket + salrec.salary;
ELSE
IF s.deptid = salrec.department THEN
IF s.maxsal <= salrec.salary THEN
s.maxsal := salrec.salary;
END IF;
IF s.minsal >= salrec.salary THEN
s.minsal := salrec.salary;
END IF;
counter := counter + 1;
bucket := bucket + salrec.salary;
ELSE
s.avgsalary := bucket/counter;
RETURN NEXT s;
s.deptid := salrec.department;
s.minsal := salrec.salary;
s.maxsal := salrec.salary;
counter := 1;
bucket := salrec.salary;
END IF;
END IF;
END LOOP;
s.avgsalary := bucket/counter;
RETURN NEXT s;
RETURN;
END ‘
LANGUAGE ‘plpgsql’ ;
— =============================
— Select statements
— =============================
—
— All data
—
select e.id as “Emp Id”, e.name as “Emp Name”, e.salary as “Salary”,
d.id as “Dept Id”, d.name as “Dept Name”
from employee e , department d
where e.departmentid = d.id;
—
— All employee department numbers
—
select * from getemployeedid(0);
—
— Each row represents on person in the department making over 50000.
— Equivalent to
— select d.id, d.name from employees e, department d
— where e.departmentid = d.id;
—
select id, name from getemployeedid(50000) e, department d where e = id;
—
— How many employees make over 50000 in each department
—
select count(*), g from getemployeedid(50000) g group by g;
—
— Department salary averages
—
select * from avgsal();
—
— Deparment salary averages with Deparment info
—
select d.name, a.minsal, a.maxsal, a.avgsalary
from avgsal() a, department d
where d.id = a.deptid;
Si quieres seguir aprendiendo con nosotros, puedes ingresar a nuestros
