You are here
Funciones y Consultas en PostgresSQL Sin categoría 

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

Cursos y Diplomados de Tecnología Web visita www.uneweb.edu.ve  para más información

Related posts

Comments

Leave a Comment


*