Manual PostgrSQL by postgreSQL

VIEWS: 2,073 PAGES: 93

More Info
									Escuela Politécnica Nacional
   Bases de Datos
     Manual
                   Autores
Chamorro Patricio


Echeverría Sara


Ibadango Dina


Rosero Andrés


Tintín Christian


Tipán Cristiam
               Temario
	¿Qué es PostgreSQL?
	Características
	Prestaciones
	Limitaciones
	Ventajas de PostgreSQL
	Sentencias DDL
  	 Creación de base de datos (DATABASE)
  	 Creación de esquemas (SCHEMAS)
  	 Creación de secuencias (SEQUENCE)
  	 creación de tablas (TABLES)
  	 Creación de restricciones (CHECK)
  	 Creación de restricciones (UNIQUE)
  	 Creación de claves primarias (PRIMARY KEYS)
  	 Creación de claves foráneas (FOREIGN KEYS)
	Sentencias DML
  	 Listar datos (SELECT)
  	 Insertar datos (INSERT)
  	 Actualizar datos (UPDATE)
  	 Eliminar datos (DELETE)
	Tipos de datos
  	 Tipos de datos del estándar SQL3
  	 Tipos de datos extendidos
  	 Tipos de datos definidos
	Creación de una base de datos
	Funciones
  	 Descripción del lenguaje
  	 Definición de variables locales o de función
  	 Asignación de las sentencias
  	 Retornando un valor como función
  	 Asignando comentarios
  	 Utilizando sentencias de control
     o    Sentencia de ciclo loop
     o    Sentencia de salida exit
     o    Sentencias if, else
     o    Sentencia de ciclo for
     o    Sentencia de ciclo condicional while
  	 Funciones SQL
  	 Funciones matemáticas
  	 Funciones string
  	 Funciones de fecha/hora
  	 Funciones de formato
	Joins (cruces)
	Operadores agregados
	Agregación por grupos
	Consultas
	Subconsultas
  	 Unión, intersección, excepción
	Transacciones
	Vistas
	Reglas
	Procedimientos almacenados
	Triggers
	Implementación de una base de datos
	Consultas propuestas
	Bibliografía
INSTALACIÓN DE
   POSTGRES
Introducción



PostgreSQL juega un papel importante porque es un sistema que tiene muchas cualidades
que hacen que sea una buena alternativa para instalar sistemas en empresas, universidades,
etc. Este documento es un material práctico en el cual le presentamos como instalas
postgreSQL en el sistema operativo Windows y linux.




                                                                                      1
Instalación de postgreSQL en Windows
Paso 1:

Acceder a página de postgreSQL (http://www.postgresql.org/) para descargarnos el
instalador de la base de datos postgreSQL.




 2
Paso 2:

Escoger el instalador para Windows.




                                      3
Paso 3:

Descarga del instalador.




 4
Paso 4:



Una vez descargado el instalador, lo ejecutamos y nos mostrara la página de inicio.




                                                                                      5
Paso 5:



En la siguiente pantalla se debe escoger el directorio de instalación,
luego presionar el botón siguiente.




6
Paso 6:



Escribir una contraseña para el super usuario, luego presionar el
botón siguiente.




                                                                    7
Paso 7:



Escribir el puerto donde se podrá conectar el programa, luego
presionar el botón siguiente.




8
Paso 8:



En opciones avanzadas, escoger la configuración regional del nuevo
clúster de base de datos, aquí se debe seleccionar Spanish, Ecuador,
luego presionar el botón siguiente.




                                                                   9
Paso 9:



Una vez configurado, el programa está listo para instalarse, al
presionar clic en el botón siguiente el programa comenzara su
instalación.




10
Paso 10:



Esperar hasta que el programa termine su instalación, una finalizada
la instalación presionar clic en el botón siguiente.




                                                                   11
Paso 11:



Presionar clic en el botón terminar y finalizar la instalación.




12
Para conectarse a la base de datos, se debe dar doble clic donde dice servidor,




luego escribimos la contraseña que se asigno en la instalación y automáticamente se
conecta al servidor del postgreSQL.




                                                                                  13
Entonces tendremos la siguiente ventana:




Ahora ya estamos listos para crear nuestra propia base de datos con todos los atributos
necesarios que necesitemos.




14
Instalación de postgreSQL en Ubuntu


Lo primero que haremos es descargar la postgreSQL de la página:

https://launchpad.net/ubuntu/karmic/i386/postgresql-8.4/8.4.0-2



El archivo que se descarga viene con extensión .deb. Así que una ves descargado el archivo
simplemente se lo debe ejecutar y listo.




Una vez instalado el servidor tenemos que configurarlo




Configuración de PostgreSQL

Por razones de seguridad estableceremos la nueva contraseña al usuario del sistema
creado por PostgreSQL:


$ sudo passwd postgres
                                                                                      15
Cambiar los privilegios de acceso al shell del usuario postgres con el siguiente
comando:


$ sudo vipw



Y cambiamos el shell del usuario postgres de “/bin/false” a “/bin/bash”. Luego
salimos grabando con escape “:wq”. Para verificar si la instalación fue satisfactoria
accedemos a la shell del servidor de bases de datos:


$ sudo su postgres -c “psql template1”



Si el acceso ha sido satisfactorio cambiamos la contraseña al usuario predeterminado
del servidor de bases de datos:


template1=# ALTER USER postgres WITH PASSWORD ‘nueva_contraseña’;



Te saldrá el siguiente mensaje confirmando la operación:

ALTER ROLE

Sal de la shell del servidor de bases de datos con el comando \q :

template1=# \q



Permitir conexiones remotas
Por motivos de seguridad, la configuración por defecto no admite conexiones
externas. Para habilitarlas tenemos que editar el fichero


/etc/postgresql/8.4/main/postgresql.conf.

$ sudo gedit /etc/postgresql/8.4/main/postgresql.conf



Ahora buscamos las siguientes líneas que se encuentran comentadas:
16
#listen_addresses = ‘localhost’



Y la substituimos por la siguiente línea:


listen_addresses = ‘*’



Posteriormente buscamos la siguiente línea y le quitamos la marca de comentario:


#password_encryption = on



Y nos debe quedar lo siguiente:


password_encryption = on



Guardamos los cambios y reiniciamos el demonio para que los cambios surjan
efecto:


$ sudo /etc/init.d/postgresql-8.4 restart


Instalación de pgadmin
Para tener un entorno gráfico que permita manipular el servidor de bases de datos
se utilizará pgadmin
Abrimos un terminal y ponemos el comando:


$ sudo apt-get install pgadmin3



Listo ahora ya tenemos pgadmin.



Este se lo puede encontrar en Aplicaciones-->Programación
                                                                             17
Cuando iniciamos por primera vez se tendrá que introducir los parámetros de
configuración del servidor de bases de datos.




Para este ejemplo se tiene los siguientes datos:
Name: postgres
host: 127.0.0.1
Username: postgres
Password: postgres

18
    ne jo
  a
M e
    d
         res
 Po  stg
Introducción


PostgreSQL juega un papel importante porque es un sistema que tiene muchas
cualidades que hacen que sea una buena alternativa para instalar sistemas en empresas,
universidades, etc. Este documento es un material práctico de uso de postgreSQL, no
se profundiza demasiado en lo que son conceptos. La información se ilustra por medio
de varios ejemplos que permiten comprender los conceptos. Parte de la información fue
obtenida de la documentación oficial de PostgreSQL, libros y manuales, de donde se obtuvo
el conocimiento necesario para generar buenas ideas que sean útiles para el documento
y así que fuera fácil de entender y lograr el objetivo de dar una visión global acerca del
sistema de bases de datos.




20
¿Qué es postgreSQL?

PostgreSQL es un avanzado sistema de bases de datos relacionales basado en Open Source.
Esto quiere decir que el código fuente del programa está disponible a cualquier persona
libre de cargos directos, permitiendo a cualquiera colaborar con el desarrollo del proyecto
o modificar el sistema para ajustarlo a sus necesidades. PostgreSQL está bajo licencia BSD
(Berkeley Software Distribution).

Un sistema de base de datos relacionales es un sistema que permite la manipulación
de acuerdo con las reglas del ´algebra relacional. Los datos se almacenan en tablas de
columnas y renglones. Con el uso de llaves, esas tablas se pueden relacionar unas con
otras.

Características

    	 PostgreSQL está bajo licencia BSD (Berkeley Software Distribution)
    Esta licencia tiene menos restricciones en comparación con otras como la GPL estando
       muy cercana al dominio público.

                 La licencia BSD al contrario que la GPL permite el uso
                   del código fuente en software no libre.
Se tiene 4 libertades del software libre, como son:

-   Usar Pgsql para fines comerciales de cualquier tipo
-   Revenderlo
-   Rebautizarlo
-   Puede hacer todo

    	 Corre en casi todos los principales sistemas operativos: Linux, Unix, BSDs, Mac OS,
       Beos, Windows, etc.

    	 Documentación muy bien organizada, pública y libre, con comentarios de los propios
       usuarios.

    	 Altamente adaptable a las necesidades del cliente.

    	 Soporte nativo para los lenguajes más populares del medio: PHP, C, C++, Perl,
       Python, etc.

    	 Soporte de tipos de datos de SQL92 y SQL99.

    	 Soporte de protocolo de comunicación encriptado por SSL.




                                                                                      21
Prestaciones

PostgreSQL destaca por su amplísima lista de prestaciones que lo hacen capaz de competir
con cualquier SGBD comercial:

     	 Está desarrollado en C, con herramientas como Yacc y Lex.

     	 La API de acceso al SGBD se encuentra disponible en C, C++, Java, Perl, PHP, Python
        y TCL, entre otros.

     	 Cuenta con un rico conjunto de tipos de datos, permitiendo además su extensión
        mediante tipos y operadores definidos y programados por el usuario.

     	 Su administración se basa en usuarios y privilegios.

     	 Sus opciones de conectividad abarcan TCP/IP, sockets Unix y sockets NT, además de
        soportar completamente ODBC.

     	 Los mensajes de error pueden estar en español y hacer ordenaciones correctas con
        palabras acentuadas o con la letra ‘ñ’.

     	 Es altamente confiable en cuanto a estabilidad se refiere.

     	 Puede extenderse con librerías externas para soportar encriptación, búsquedas por
        similitud fonética (soundex), etc.

     	 Control de concurrencia multiversión, lo que mejora sensiblemente las operaciones
        de bloqueo y transacciones en sistemas multiusuario.

     	 Soporte para vistas, claves foráneas, integridad referencial, disparadores,
        procedimientos almacenados, subconsultas y casi todos los tipos y operadores
        soportados en SQL92 y SQL99.

     	 Implementación de algunas extensiones de orientación a objetos. En PostgreSQL es
        posible definir un nuevo tipo de tabla a partir de otra previamente definida.




22
Limitaciones

   	 Máximo de base de datos: ILIMITADO
   	 Máximo de tamaño de tabla: 32TB
   	 Máximo de tamaño de registro: 1.6TB
   	 Máximo de tamaño de campo: 1GB
   	 Máximo de registros por tabla: ILIMITADO
   	 Máximo de campos por tabla: 250 a 1600 (depende de los tipos usados)
   	 Máximo de índices por tabla: ILIMITADO
   	 Número de lenguajes en los que se puede programar funciones: aproximadamente
      10 (pl/pgsql, pl/java, pl/perl, pl/python, tcl, pl/php, C, C++, Ruby, etc.)
   	 Métodos de almacenamiento de índices : 4 (B-tree, Rtree, Hash y GisT)

Ventajas de PostgreSQL

PostgreSQL se caracteriza por ser un sistema estable, de alto rendimiento, gran flexibilidad
ya que funcionar la mayoría de los sistemas Unix, además tiene características que
permiten extender fácilmente el sistema. PostgreSQL puede ser integrada al ambiente
Windows permitiendo de esta manera a los desarrolladores, generar nuevas aplicaciones
o mantener las ya existentes. Permite desarrollar o migrar aplicaciones desde Access,
Visual Basic, FoxPro, Visual FoxPro, C/C++ Visual C/C++, Delphi, etc., para que utilicen
a PostgreSQL como servidor de BD; Por lo expuesto PostgreSQL se convierte en una gran
alternativa al momento de decidirse por un sistema de bases de datos.




                                                                                       23
Sentencias DDL
El Lenguaje de Definición de Datos (Data Definition Language – DDL), sirve para modificar
la estructura de los objetos en una base de datos.

Estas sentencias básicamente son: CREATE, ALTER, DROP y TRUNCATE.

A continuación iniciaremos con la creación de una base de datos.

Creación de base de datos (DATABASE):

Sintaxis:
        CREATE DATABASE name [ WITH LOCATION = ‘dbpath’ ]

Ejemplo:
        CREATE DATABASE bddPedidos TEMPLATE template0;



Con la línea CREATE DATABASE bddPedidos TEMPLATE template0; , se crea una tabla a
partir de una plantilla que trae postgres llamada template0, el cual nos crea la base de
datos totalmente vacía.

Creación de esquemas (SCHEMAS):

Los esquemas son importantes para agrupar objetos según especificaciones, y al
mantener organizado la base de datos permite un mejor desempeño al momento de la
administración.

Si no se le asigna un esquema al objeto, postgres lo asignará implícitamente al esquema
“public“. Para definir que un objeto pertenezca a un esquema se pone el nombre del
esquema seguido por un ponto y el nombre del objeto.

        CREATE SCHEMA Persona;

        CREATE SCHEMA Admision;



Creación de secuencias (SEQUENCE):

CREATE SEQUENCE Persona.seq_idcontacto START 1; --empieza desde 1

CREATE SEQUENCE Admision.seq_idmodalidad START 1; --empieza desde 1



Creación de tablas (TABLES):

Sintaxis:

CREATE [ TEMPORARY | TEMP ] TABLE table ( column type [ NULL | NOT NULL ]
[ UNIQUE ] [ DEFAULT value ] [column_constraint_clause | PRIMARY KEY } [ ... ]
] [, ... ] [, PRIMARY KEY ( column [, ...] ) ] [, CHECK ( condition ) ] [, table_
constraint_clause ] ) [ INHERITS ( inherited_table [, ...] ) ]

24
Ejemplo:

        create table prueba (FechaCreacion date not null default now)

Con “FechaCreacion date not null default now(),” asignaremos la fecha actual por defecto
al campo FechaCreacion por medio de la función now().

Creación de restricciones (CHECK):

Los valores dentro de CHECK , por ejemplo “CHECK (Genero in (‘1’,’2’, ‘3’, ‘4’,))”, son los
únicos que serán permitidos insertar en el campo establecido, en este caso se le asigna esa
restricción al campo Genero.

Ejemplo:

        ALTER TABLE Empleados

        ADD CONSTRAINT ck_Reporta

        CHECK (reporta_a in (‘1’,’2’, ‘3’, ‘4’,))

        ;

Creación de restricciones (UNIQUE):

Las restricciones UNIQUE, por ejemplo “UNIQUE(nombreprov)” , aseguran que en los datos
en la misma columna, en este caso en el campo nombreprov, no sean repetidas, ya que no
puede haber registradas 2 nombres de proveedores con el mismo nombre.

Sintaxis:

CREATE [ UNIQUE ] INDEX nombre_indice ON tabla         [ USING nombre_acceso ](
columna [nombre_operador][, ...] )

Ejemplo:

        ALTER TABLE Proveedores

        ADD CONSTRAINT uq_nombreprov

        UNIQUE(nombreprov)



Creación de claves primarias (PRIMARY KEYS):

Las claves primarias, aparte de prevenir la duplicidad de datos, y servir como nexo para
relacionarse con otras tablas, tiene otro fin también importante, que es agilizar el proceso
de búsqueda, no por el hecho de ser primary key, sino que al momento de la creación de
una clave primaria se crea implícitamente un indice (index).

Sintaxis:

[ CONSTRAINT name ] { [        NULL | NOT NULL ] | PRIMARY KEY } [, ...]

Ejemplo:

        ALTER TABLE Proveedores

        ADD CONSTRAINT pk_Proveedorid
                                                                                       25
       PRIMARY KEY(Proveedorid)

       ;

Creación de claves foráneas (FOREIGN KEYS):

Las claves foráneas son campos que servirán para la relación entre 2 tablas, la clave
primaria de otra tabla se relacionará con la clave foránea de ésta.

Ejemplo:

       ALTER TABLE Productos

       ADD CONSTRAINT fk_ Proveedorid

       FOREIGN KEY(Proveedorid)

       references Proveedores (Proveedorid)




26
Sentencias DML
El lenguaje de Manipulación de Datos (Data Manipulation Language – DML) sirve para llevar
a cabo las transacciones en las base de datos, entiéndase por transacciones los procesos
de inserción, actualización, eliminación, selección.

Para mayor explicación se expone los siguientes ejemplos usando la base de datos
PEDIDOS:

Listar datos de Clientes

Sintaxis:

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] expression [ AS name ] [,
...] [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] [ FROM table [ alias ] [, ...]
] [ WHERE condition ] [ GROUP BY column [, ...] ] [ HAVING condition [, ...] ] [
{ UNION [ ALL ] | INTERSECT | EXCEPT } select ] [ ORDER BY column [ ASC | DESC |
USING operator ] [, ...] ] [ FOR UPDATE [ OF class_name [, ...] ] ] LIMIT { count |
ALL } [ { OFFSET | , } start ]



Ejemplo:

         Select * from clientes ; --Se lista todos los elementos de una tabla

         Select Clienteid, nombrecia,celular from clientes --Se lista ciertos campos de una
tabla

Insertar datos en Clientes

Sintaxis:

      INSERT INTO table [ ( column [, ...] ) ]    { VALUES ( expression [, ...] ) | SELECT
query }

Ejemplo:

        INSERT INTO CLIENTES

       VALUES (10,’183445667’,’YARBANTRELLA’,’PABLO POLIT’,’AV.REPUBLICA’,NULL,NU
LL,NULL,NULL);



         SELECT * FROM clientes; --Comprobar Inserción




                                                                                       27
Actualizar datos en Clientes

Sintaxis:

UPDATE tabla SET columna = expresion [, ...]   [ FROM lista ]   [ WHERE condicion ]

Ejemplo:

       UPDATE clientes set direccioncli =’AV. PATRIA ‘

       WHERE clienteid =1;

       SELECT * FROM clientes;          --Comprobar Actualización



Eliminar datos en Clientes

Sintaxis:

       DELETE FROM table [ WHERE condition ]

Ejemplo:

       DELETE FROM Clientes

       WHERE clienteid =’1’;

       SELECT * FROM clientes;          --Comprobar Eliminación




28
TIPOS DE DATOS




             29
Como todos los manejadores de bases de datos, PostgreSQL implementa los tipos de datos
definidos para el estándar SQL3 y aumenta algunos otros.

Los definidos por el estándar SQL3 son los siguientes:
         Tipos de datos del estándar SQL3 en PostgreSQL
Tipo en Postgres Correspondiente en SQL3 Descripción
                                          valor lógico o booleano
Bool             boolean
                                          (true/false)
                                          cadena de carácteres de
char(n)          character(n)
                                          tamaño fijo
Date             date                     fecha (sin hora)
                                          número de punto flotante
float4/8         float(86#86)
                                          con precisión 86#86
                                          número de punto flotante
float8           real, double precision
                                          de doble precisión
                                          entero de dos bytes con
int2             smallint
                                          signo
                                          entero de cuatro bytes
int4             int, integer
                                          con signo
                                          número exacto con
int4             decimal(87#87)
                                          88#88
                                          número exacto con
int4             numeric(87#87)
                                          89#89
Money            decimal(9,2)             cantidad monetaria
                                          hora en horas, minutos,
Time             time
                                          segundos y centésimas
Timespan         interval                 intervalo de tiempo
                                          fecha y hora con
Timestamp        timestamp with time zone
                                          zonificación
                                          cadena de carácteres de
varchar(n)       character varying(n)
                                          tamaño variable


Los tipos de datos extendidos en PostgreSQL son:
    Tipos de datos extendidos en
             PostgreSQL
Tipo     Descripción
box      caja rectangular en el plano
         dirección de red o de host en
cidr     IP versión 4
circle   círculo en el plano
         dirección de red o de host en
inet     IP versión 4
         entero de ocho bytes con
int8
         signo
line     línea infinita en el plano
lseg     segmento de línea en el plano


30
        trayectoria geométrica, abierta
path    o cerrada, en el plano
point   punto geométrico en el plano
        trayectoria geométrica cerrada
polygon
        en el plano
serial  identificador numerico único


Tipos de datos definidos por PostgreSQL:
Tipo        Descripción
SET         conjunto de tuplas
abstime     fecha y hora absoluta de rango limitado (Unix system time)
aclitem     lista de control de acceso
bool        booleano ‘true’/’false’
box         rectángulo geométrico ‘(izquierda abajo, derecha arriba)’
            carácteres rellenos con espacios, longitud especificada al momento
bpchar
            de creación
bytea       arreglo de bytes de longitud variable
char        un sólo carácter
            command identifier type, identificador de secuencia en
cid
            transacciones
cidr        dirección de red
circle      círculo geométrico ‘(centro, radio)’
date        fecha ANSI SQL ‘aaaa-mm-dd’
datetime    fecha y hora ‘aaaa-mm-dd hh:mm:ss’
filename    nombre de archivo usado en tablas del sistema
float4      número real de precisión simple de 4 bytes
float8      número real de precisión doble de 8 bytes
inet        dirección de red
int2        número entero de dos bytes, de -32k a 32k
int28       8 numeros enteros de 2 bytes, usado internamente
int4        número entero de 4 bytes, -2B to 2B
int8        número entero de 8 bytes, 90#9018 dígitos
line        línea geométrica ‘(pt1, pt2)’
lseg        segmento de línea geométrica ‘(pt1, pt2)’
macaddr     dirección MAC
money       unidad monetaria ‘$d,ddd.cc’
name        tipo de 31 carácteres para guardar identificadores del sistema
numeric     número de precisión múltiple
oid         tipo de identificación de objetos
oid8        arreglo de 8 oids, utilizado en tablas del sistema
path        trayectoria geométrica ‘(pt1, ...)’
point       punto geométrico ‘(x, y)’
polygon     polígono geométrico ‘(pt1, ...)’
regproc     procedimiento registrado
reltime     intervalo de tiempo de rango limitado y relativo (Unix delta time)
smgr        manejador de almacenamiento (storage manager)
text        cadena de carácteres nativa de longitud variable
tid         tipo de identificador de tupla, localización física de tupla
time        hora ANSI SQL ‘hh:mm:ss’
timespan    intervalo de tiempo ‘@ <number> <units>’
                                                                                 31
timestamp fecha y hora en formato ISO de rango limitado
tinterval intervalo de tiempo ‘(abstime, abstime)’
unknown   tipo desconocido
          cadena de carácteres sin espacios al final, longitud especificada al
varchar
          momento de creación
xid       identificador de transacción




32
  CREACIóN
   DE UNA
BASE DE DATOS




                33
Para la creación de una nueva base de datos se debe dar clic en una de las bases de
datos que están creadas y abrir un nuevo texto de consultas




y aquí utilizamos la siguiente sintaxis para poder crear una nueva base de datos:

CREATE DATABASE nombre_bases_de_datos

Ejemplo:

CREATE DATABASE PEDIDOS

Creación de Tablas
Las tablas forman parte de una base de datos.
Para crear una tabla debemos resolver qué campos (columnas) tendrá y qué tipo de
datos almacenarán cada uno de ellos, es decir, su estructura.
Sintaxis:
create table NOMBRETABLA(
 NOMBRECAMPO1 TIPODEDATO,
 ...
 NOMBRECAMPON TIPODEDATO
);

Ejemplo:

CREATE TABLE EMPLEADOS(
     EMPLEADOID int NOT NULL,
     NOMBRE char(30) NULL,
     APELLIDO char(30) NULL,
     FECHA_NAC date NULL,
     REPORTA_A int NULL,
     EXTENSION int NULL,
 CONSTRAINT PK_EMPLEADOS PRIMARY KEY (EMPLEADOID));
34
DROP TABLE

El comando DROP TABLE permite eliminar una tabla, incluyendo todas las tuplas
almacenadas en ella:

Sintaxis:
DROP TABLE table_name;

Ejemplo:
DROP TABLE CATEGORIAS;




INSERT INTO

Para llenar una tabla previamente creada se hace el uso del comando INSERT INTO,
que permite llenar todos los campos que la tabla tiene.

Sintaxis:
INSERT INTO table_name (name_of_attr_1
[, name_of_attr_2 [,...]])
VALUES (val_attr_1
[, val_attr_2 [, ...]]);

Ejemplo:
insert into categorias (categoriaid, nombrecat) values (100, ‘CARNICOS’);
insert into categorias (categoriaid, nombrecat) values (200, ‘LACTEOS’);
insert into categorias (categoriaid, nombrecat) values (300, ‘LIMPIEZA’);

FUNCION UPDATE

Cambia los valores de columnas especificadas en todas las filas que satisfagan cierta
condición. En la clausula SET se deben mencionar solamente las columnas a ser modificadas;
el resto de columnas no mencionadas retendrán sus valores anteriores.
Por defecto, UPDATE puede actualizar una tabla específica y todas sus subtablas. Si se
desea actualizar únicamente cierta tabla, se debe utilizar la clausula ONLY.

Existen dos maneras de modificar una tabla utilizando la información contenida en otras
tablas en la base de datos: utilizando subconsultas o especificando las tablas adicionales
en la clausula FROM. La técnica más apropiada depende de las circunstancias específicas.

Sintaxis:

        UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
                [ FROM fromlist ]
                [ WHERE condition ]




                                                                                     35
Donde:

table: Nombre de la tabla a actualizar.

column: Nombre de la columna de la tabla. El nombre de la columna puede calificarse
como nombre de un subcampo o subíndice del arreglo, si es necesario.

expression: Expresión para asignar a la columna. La expresión podría utilizar los
antiguos valores de esa y otras columnas en la tabla.

DEFAULT: Asigna el valor predefinido a la columna (qué será NULL si ninguna expresión
predefinida específica se ha asignado a él).

fromlist : Una lista de expresiones de la tabla, permitiendo la aparición de columnas de
otras tablas en la condición WHERE y las expresiones de actualización. Esto es similar
a la lista de tablas en que pueden especificarse en la función FROM de una sentencia
SELECT. La tabla designada no debe aparecer en el fromlist, a menos que se inserte un
self-join (en tal caso debe aparecer con un alias).

condition: Una expresión que devuelve un valor del tipo booleano. Solamente pueden
actualizadas las filas para las cuales devuelve el valor true.


UPDATE count
count es el número de filas actualizadas. Si el valor de count es igual a cero, ninguna fila
cumple con la condición (no quiere decir que ha ocurrido un error).

Notas: Cuando una clausula FROM está presente, lo que ocurre esencialmente es que la
tabla designada se une a las tablas mencionadas en el fromlist, y cada fila resultante en
la unión representa una operación de actualización para la tablas designadas. Cuando se
utiliza FROM se debe asegurar que la unión produce a lo mucho una fila resultante para
cada fila a ser modificada. En otras palabras, una fila designada no debe unir a más de una
fila en la otra tabla(s). Si lo hace, entonces solamente se utilizará una fila para actualizarla
cuya selección es aleatoria.

Debido a esta indeterminación, es más seguro hacer referencia a otras tablas dentro
de subconsultas de SELECT, aunque a menudo es más difícil leer y más retardado que
utilizando una unión.

Ejemplo:

Actualizar el nombre de la categoría HIGINE PERSONAL a HIGIENE PERSONAL, en la
columna nombrecat:

UPDATE CATEGORIAS SET nombrecat = ‘HIGIENE PERSONAL’ WHERE nombrecat = ‘HIGINE
PERSONAL’;




36
ALTER TABLE
Cambia la definición de una tabla existente. Existen varias subformas:

ADD COLUMN
Añade una nueva columna a la tabla utilizando la misma sintaxis que CREATE TABLE.

DROP COLUMN
Elimina una columna de la tabla. Los índices y constraints que involucra la columna
deberán ser automáticamente borrados también. Si algún elemento externo depende de
la columna, será necesario utilizar la clausula CASCADE, por ejemplo, referencias a claves
foráneas o vistas.

SET/DROP DEFAULT
Coloca o remueve el valor predefinido de una columna. El valor predefinido sólo se aplica a
las órdenes INSERT subsecuentes. También pueden crearse valores predeterminados para
vistas, en el caso en que son insertadas en las declaraciones INSERT antes de que la regla
de la vista ON INSERT sea aplicada.

SET/DROP NOT NULL
Cambian si una columna es marcada para permitir valores o rechazar los valores nulos.

SET STATISTICS
Coloca un valor de estadísticas recogidas por columna para una operación ANALIZE
subsecuente. El valor puede estar en el rango de 0 a 1000; alternativamente, se usa -1
para revertir el uso del sistema de valores de estadísticas predeterminado.

SET STORAGE
Define el modo de almacenamiento de una columna. Este controla si dicha columna depende
de otra tabla suplementaria, y si los datos deben comprimirse o no. PLAIN debe utilizarse
para valores de longitud fija como el entero interno no comprimible. MAIN es para datos
ligados comprimibles. EXTERNAL es para datos externos no comprimibles, y EXTENDED es
para datos externos comprimibles. EXTENDED es el valor predeterminado para todo tipo
de datos que lo soportan.

SET WITHOUT OIDS
Quita el identificador de columna de la tabla. La remoción de OIDs de una tabla no ocurre
inmediatamente. El espacio que ocupa el OID se guardará cuando la fila sea actualizada.
Si no se actualiza la fila, el espacio y el valor del OID son guardados indefinidamente. Esta
semántica es similar a la del proceso DROP COLUMN.

RENAME
Cambia el nombre de una tabla (o un índice, secuencia, o vista) o el nomre de una columna
individual en la tabla. No hay efecto en el almacenamiento de datos.

ADD table_constraint
Añade un nuevo constraint a la tabla usando la misma sintaxis que CREATE TABLE.

DROP CONSTRAINT
Elimina los constraints de una tabla. Actualmente, loc constraints de las tablas no requieren
tener nombres únicos, entonces pueden haber más de un constraint identidificados los
                                                                                        37
cuales serán eliminados.

OWNER
Cambia el propietario de una tabla, índice, secuencia, o vista de un usuario especificado.

CLUSTER
Marca una tabla para futuras operaciones CLUSTER. La clausula ALTER TABLE solo puede
ser ejecutada por el propietario de la tabla, mientras la clausula ALTER TABLE OWNER, sólo
puede ser ejecutada por un superusuario.

Sintaxis:

ALTER TABLE [ ONLY ] name [ * ]
  ADD [ COLUMN ] column type [ column_constraint [ ... ] ]

ALTER TABLE [ ONLY ] name [ * ]
  DROP [ COLUMN ] column [ RESTRICT | CASCADE ]

ALTER TABLE [ ONLY ] name [ * ]
  ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }

ALTER TABLE [ ONLY ] name [ * ]
  ALTER [ COLUMN ] column { SET | DROP } NOT NULL

ALTER TABLE [ ONLY ] name [ * ]
  ALTER [ COLUMN ] column SET STATISTICS integer

ALTER TABLE [ ONLY ] name [ * ]
  ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

ALTER TABLE [ ONLY ] name [ * ]
  SET WITHOUT OIDS

ALTER TABLE [ ONLY ] name [ * ]
  RENAME [ COLUMN ] column TO new_column

ALTER TABLE name
  RENAME TO new_name

ALTER TABLE [ ONLY ] name [ * ]
  ADD table_constraint

ALTER TABLE [ ONLY ] name [ * ]
  DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]

ALTER TABLE name
  OWNER TO new_owner

ALTER TABLE name
  CLUSTER ON index_name




38
Donde:

name: Nombre de una tabla existente para alterarla. Si se desea alterar una sola tabla,
se debe utilizar la clausula ONLY. Caso contrario, se alterarán la tabla y todas las tablas
que desciendan de ésta.

column: Nombre de una columna ya existente o nueva.

type: Tipo de dato para la nueva columna.

new_column: Nombre nuevo para una columna existente.

new_name: Nombre nuevo para la tabla.

table_constraint: Nuevo constraint para la tabla.

constraint_name: Nombre de un constraint existente para eliminarlo.

new_owner: Nombre del usuario o del nuevo propietario de la tabla.

index_name: Nombre del índice que indica la tabla marcada para CLUSTER.


CASCADE
Elimina automáticamente objetos que dependen de una columna o constraint eliminado.

RESTRICT
Negación a eliminar la columna o constraint si hay objetos dependientes. Esta es una
conducta predeterminada.

Notas:

La palabra clave columna puede ser omitida.
En la implementación actual de las clausulas ADD COLUMN, default y NOT NULL para la
nueva columna no son soportadas. La nueva columna siempre entra teniendo todos los
valores nulos. Se puede utilizar la forma SET DEFAULT de ALTER TABLE para poner el valor
predeterminado después. Para marcar la columna como no nula, se utiliza la forma SET
NOT NULL, después de haber ingresado los valores no nulos el las filas de la columna.

La forma DROP COLUMN no elimina la columna físicamente, simplemente la hace invisible
para las operaciones de SQL. Subsecuentemente las operaciones INSERT y UPDATE podrían
almacenar valores nulos en esta columna. Así, eliminar una columna es fácil pero no
reduce inmediatamente el tamaño en disco de la tabla, ya que el espacio ocupado por la
tabla eliminada no puede reutilizarse a menos que se realice una operación UPDATE sobre
las filas.

Si una tabla tiene tablas descendientes, no es permitido añadir o renombrar una columna
en la tabla padre sin hacer lo mismo en las descendientes. Es decir, ALTER TABLE ONLY
será rechazado. Esto asegura que las tablas descendientes siempre estén emparejadas a
la tabla padre.
                                                                                        39
Una operación recursiva DROP COLUMN sólo quitará la columna de una tabla descendiente,
si ésta no hereda esa columna de cualquier padre. Una operación no recursiva DROP
COLUMN (ALTER TABLE ONLY… DROP COLUMN) nunca remueve columnas descendientes,
en cambio las marca como herencia.

Ejemplos:

Añadir una nueva columna del tipo varchar a la tabla EMPLEADOS:

ALTER TABLE EMPLEADOS ADD COLUMN Direccion varchar(30);

Eliminar una columna de una tabla:

ALTER TABLE EMPLEADOS DROP COLUMN Direccion RESTRICT;

Renombrar una columna existente de la tabla DETALLE_ORDENES

ALTER TABLE DETALLE_ORDENES RENAME COLUMN CANTIDAD TO QUANTITY;




40
CONSULTAS SQL




                41
WHERE

En este ejemplo sólo se devuelven las filas de la tabla Productos que suministra el proveedor de
código 10 y el producto que entrega es ‘SALCHICHAS’

          SELECT descripcion, preciounit AS “Precio unitario”
          FROM productos
          WHERE proveedorid = 10
          AND descripcion LIKE ‘SALCHICHAS %’
     •	   Operadores de comparación (como =, < >, < y >).

          -- Muestre los productos que tengan un precio mayor a 2.60

          SELECT descripcion, preciounit AS “Precio unitario”
          FROM productos
          WHERE preciounit >2.60

     •	   Intervalos (BETWEEN y NOT BETWEEN).

          -- Muestre los productos con precios entre 9 y 15

          SELECT descripcion, preciounit AS “Precio unitario”
          FROM productos
          WHERE preciounit BETWEEN 9 AND 15
          ORDER BY preciounit
          -- Muestre los productos con precios fuera de 9 y 15
          SELECT descripcion, preciounit AS “Precio unitario”
          FROM productos
          WHERE preciounit NOT BETWEEN 9 AND 15
          ORDER BY preciounit

     •	   Listas (IN, NOT IN).

          -- Muestre las categorías que sean CARNICOS, LACTEOS o MEDICINAS

          SELECT *
          FROM categorias
          WHERE nombrecat IN (‘CARNICOS’,’LACTEOS’,’MEDICINAS’)

     •	   Coincidencia de modelos (LIKE y NOT LIKE).

          -- Muestre los empleados cuyo nombre empiece con J

          SELECT *
          FROM empleados
          WHERE nombre LIKE ‘J%’

          -- Muestre los empleados cuyo nombre no empiece con J

          SELECT *
          FROM empleados
          WHERE nombre NOT LIKE ‘J%’


42
   •	   Valores NULL (ISNULL y NOTNULL).

--Muestra las órdenes donde no existe descuento

        SELECT *
        FROM ordenes
        WHERE descuento ISNULL

        --Muestra las órdenes donde existe descuento

        SELECT *
        FROM ordenes
        WHERE descuento NOTNULL




                                                       43
     FUNCIONES




44
EL LENGUAJE DE FUNCIONES PL/pgSQL

Una de las características de PostgreSQL es que permite al usuario o administrador de la
base de datos escribir sus propias funciones dentro de la base de datos, estas funciones
se almacenan y ejecutan desde el proceso de base de datos y no desde la aplicación del
cliente.


PostgreSQL permite en este sentido crear sus funciones de usuario o comúnmente
llamadas “PROCEDIMIENTOS ALMACENADOS” no solo en lenguaje SQL que es un
lenguaje que carece de estructuras de control como IF, WHILE, FOR y otros, sino que
permite definir un lenguaje propio y extenderlo a la base de datos, es así como están
disponibles para Postgre los siguientes lenguajes: C, Perl, Phyton, PHP entre otros. Estos
lenguajes deben ser instalados en la base de datos previamente antes de ser utilizados.

Sin embargo, tambien existe un lenguaje propio de PostgreSQL, denominado “PL/pgsql”, que
posee estructuras de control y permite aprobechar toda la potencia de SQL para crear nuestras
propias funciones, es este lenguaje de funciones en el que concentraremos este manual.


VENTAJAS DEL USO DE FUNCIONES

•	 Desde el momento en que podemos crear nuestras propias funciones en la base de
   datos, obtenemos una serie de ventajas en nuestras aplicaciones, es así como podemos
   mesionar las siguientes ventajas del uso de funciones definidas por el usuario :

•	   Se pueden crear funciones para ser ejecutadas en algún evento de la base de datos. Al
     borrar, modificar o insertar datos por ejemplo.

•	   Se pueden añadir estructuras de control al lenguaje SQL, podemos ahora definir ciclos
     de ejecución como FOR y WHILE que nos permitan recorrer variables de tipo array,
     tablas u otras estructuras.

•	   Las funciones nos permiten realizar cálculos complejos, así ampliamos las funciones
     pre-definidas por el motor de base de datos y construir funciones a medida de nuestras
     necesidades o las del cliente/empresa.

•	   Se pueden heredar todos los tipos definidos por el usuario, las funciones y los operadores,
     esta característica nos permite tomar variabless de tipo registro o fila ( RECORD o ROW)
     y permitirnos procesar sus datos.

•	   Nos aporta una ejecución fiable para el servidor, es decir, podemos estar seguros de de
     el conjunto de instrucciones de la función definida se ejecutará, sin importar si ocurren
     problemas en el cliente, en una conexión o enlace, una vez solicitada la función esta se
     ejecuta en modo seguro y protegido en el servidor.

Es un lenguaje fácil de usar, pues extiende de una manera simplista pero a la vez potente
el lenguaje SQL.

                                                                                           45
DESCRIPCION DEL LENGUAJE
ESTRUCTURA DE PL/pgSQL
El lenguaje PL/pgSQL no es sensible al reconocimiento de mayúsculas. Todas las palabras
clave e identificadores pueden usarse en cualquier mescla de mayúsculas y minúsculas,
sin embargo, de preferencia, el nombre de tablas y de registros utilice según definió en la
base de datos.

PL/pgSQL es un lenguaje orientado a bloques. la estructura básica del lenguaje se define
de la siguiente manera :

       CREATE [OR REPLACE] FUNCTION <nombre_de_funcion> ([lista de parámetros])
       RETURNS <tipo_de_retorno> AS $$
       DECLARE
          <declaración de variables locales de la función>
       BEGIN
          <sentencias propias de la función>
       END;
       $$LANGUAGE ‘plpgsql’;


DEFINIENDO LOS PARAMETROS DE LAS FUNCIONES

Una de las características de PL/pgSQL es que no es necesario definir los nombres de
variables en la función, sólo es necesario definir si tipo, de de esta manera tenemos que
una función puede ser definida en términos de par{ametros de entrada y salida de la
siguiente forma :

       FUNCTION suma ( real , real )
       RETURNS real AS ‘
       /*...resto de la funcion... */

       En este ejemplo definimos la función “suma” con dos parametros de entrada de
       tipo real y tambien la salida o RETURNS de tipo real.
Cabe notar que podemos utilizar como parametros todos los tipos usados por PostgreSQL,
es decir : INTEGER, DATE, TIME, VARCHAR, CHAR, TIMESTAMP, REAL, TEXT y muchos
otros, e inclusive tipos definidos por el usuario por medio de la instrucción CREATE TYPE.



     •	 DEFINICION DE VARIABLES LOCALES O DE FUNCION

Como explicamos anteriormente, las variables locales son definidas dentro de la sección
DECLARE de la función, de forma muy similar a otros lenguajes podemos definir variables,
por ejemplo :

       DECLARE
       contador integer = 0;
       cadena varchar;
       fecha date;
       continuar bool;
       suma real;
46
En este caso definimos una serie de variables con su tipo de dato, tambien, podemos
definir una variable y asignarle un valor en la misma línea de código de la definición de la
variable (note la variable “contador” inicializada en cero).

      FUNCTION suma (real, real )
      RETURNS real AS ‘
      DECLARE
      numero_1 alias for $1;
      numero_2 alias for $2;
      valor_total real;
      /*...resto de la funcion... */


Continuando nuestra función suma, podemos apreciar que en la definición de variables
usamos una nomenclatura especial para asignar nombres de variable a los parámetros de
entrada, de hecho, si no hace esta redefinición, puede los parámetros asignando “$” y el
número indicador en la lista de parámetros a cada variable de parámetro, por ejemplo :
valor_total = $1 + $2;



   •	 ASIGNACION DE LAS SENTENCIAS DE PL/pgSQL

Una vez definidos los parámetros de entrada/salida, y tambien nuestras variables locales,
podemos comenzar a trabajar en nuestra función en la sección BEGIN .... END;

Las asignaciones de valores se realizan de la misma forma que en PASCAL, es decir,
utilizando = como operador de asignación y punto y coma al final de la línea, ejemplo :

      FUNCTION suma (real, real )
      RETURNS real AS ‘
      DECLARE
      numero_1 alias for $1;
      numero_2 alias for $2;
      valor_total real;
      BEGIN
      valor_total = numero_1 + numero_2;
      numero_1 = numero_1 * numero_2;
      /*...resto de la funcion... */


Como vemos en este ejemplo la asinación de valores es prácticamente igual a PASCAL.


   •	 RETORNANDO EL UN VALOR COMO FUNCION

Finalmente una vez procesados nuestros valores debemos retornar algún valor (pues ese
es la esencia de la función), este retorno de valores también se realiza dentro de la sección
BEGIN de la función, pero utilizando la siguiente forma básica :

      /*...resto de la funcion... */
      RETURN <variable>;
      /*...resto de la funcion... */

                                                                                        47
Ejemplo completo de la función SUMA :

        FUNCTION suma ( real , real )
        RETURNS real AS ‘
        DECLARE
        numero_1 alias for $1;
        numero_2 alias for $2;
        valor_total real;
        BEGIN
        valor_total = numero_1 + numero_2;
        RETURN valor_total;
        END;
        $$LANGUAGE ‘plpgsql’;

Poco a poco hemos ido creando esta función, ahora está terminada y lista para ser
utilizada.


     •	 ASIGNANDO COMENTARIOS

Si así lo necesitas, puedes documentar tus funciones usando los operadores /* ... */ para
crear un comentario de bloque multilinea o bien -- para un comentario de linea sencilla,
ejemplo :

        /*...resto de la función... */
        /* Este es un comentario
        que utiliza multiples líneas de comentario */
        -- Este es un comentario en una sola línea
        /*...resto de la función... */
     •	 UTILIZANDO SENTENCIAS DE CONTROL

Podemos utilizar sentencias de control para escribir nuestros programas y permitir que sigan
un algoritmo no lineal, para ello, contamos básicamente con las siguientes estructuras:

                                                 LOOP
                                                 EXIT
                                               IF, ELSE
                                                  FOR
                                                WHILE


     •	 SENTENCIA DE CICLO LOOP :

Esta sentencia nos permite efectuar un ciclo, su estructura básica es :

     /*...resto de la funcion... */
     LOOP
     ...sentencias a ejecutar en el ciclo...
     END LOOP
     /*...resto de la funcion... */



48
   •	 SENTENCIA DE SALIDA EXIT :

Cuando usamos un ciclo LOOP, es necesario darle una salida para que este mismo termine,
la sentencia EXIT nos permite finalizar el LOOP y continuar en la sentencia siguente a la
salida de éste, generalmente, EXIT está acompañado con la sentencia IF, por ejemplo

   LOOP
     /*...resto de la funcion... */
      contador = contador + 1;
      IF contador >= 10 THEN
         EXIT; -- salida del loop cuando sea mayor o igual que 10
      END IF;
   END LOOP
   /*...resto de la funcion... */

   •	 SENTENCIAS IF, ELSE :

Podemos efectuar operaciones de comparación con las sentencias IF, ELSE, los operadores
de comparación son los siguientes :

      < menor que ...
      > mayor que ...
      <> distinto a ...
      <= menor o igual que ...
      >= mayor o igual que ...
      = igual que ...
      OR o ...
      AND y ...


Ejemplo :

      /*...resto de la funcion... */
      IF >= 10 or THEN
         RETURN true;

      ELSE
         RETURN false;
      END IF;
      /*...resto de la funcion... */

   •	 SENTENCIA DE CICLO FOR :

Existen dos tipos de ciclos FOR, el primero tiene que ver claramente con los ciclos numéricos
comunes, es decir, lo que comunmente se usa para efectuar repeticiones, y por otra
parte, tenemos un ciclo FOR que nos permite recorrer tablas y procesar sus datos, esta
segunda está ligada a sentencias SQL, veamos entonces como se utiliza la sentencia FOR

CASO 1 : SENTENCIA FOR DE PROCESO CICLICO NUMERICO

      FOR <variable> IN <inicio>..<fin> LOOP
        ... SENTENCIAS A EJECUTAR DENTRO DEL CICLO FOR ...
      END LOOP;
                                                                                        49
o bien puede usar el FOR de forma inversa

       FOR <variable> REVERSE <fin>..<inicio> LOOP
         ... SENTENCIAS A EJECUTAR DENTRO DEL CICLO FOR ...
       END LOOP;


Ejemplo :

       /*...resto de la funcion... */
       FOR inc IN 1..10 LOOP
          factorial = factorial * inc;
       END LOOP;
       RETURN factorial;
       /*...resto de la funcion... */



CASO 2: SENTENCIA FOR APLICADA A PROCESOS DE REGISTROS SQL

       FOR <registro o fila> IN <sentencia SELECT SQL> LOOP
       ... SENTENCIAS A EJECUTAR DENTRO DEL CICLO FOR ...
       END LOOP;


Ejemplo :

       /*...resto de la funcion... */
       DECLARE
          registro RECORD;
       BEGIN
       FOR registro IN SELECT * FROM productos LOOP
          stock = registro.sock_actual + 100;
         /*... otras sentencias ... */
       END LOOP;
       /*...resto de la funcion... */


Note que para utilizar esta sentencia FOR debemos declarar una variable de tipo RECORD
o registro. RECORD es una palabra reservada para estos casos, y no posee una estructura
definida pues no ha sido asignada a ninguna tabla, este tipo de dato nos permite recorrer
la tabla de productos y obtener sus valores por medio de esta variable.

     •	 SENTENCIA DE CICLO CONDICIONAL WHILE

La sentencia WHILE se ejecuta de forma muy similar a otros lenguajes de programación,
su estructura es la siguiente :

       WHILE <condicion> LOOP
         ... SENTENCIAS A EJECUTAR DENTRO DEL CICLO WHILE ...
       END LOOP;

Ejemplo :
     /*...resto de la funcion... */
     WHILE inc <= 10 LOOP

50
         factorial = factorial * inc;
      END LOOP;
      RETURN factorial;
      /*...resto de la funcion...*/


Este codigo funciona igual al descrito en el ejemplo del ciclo FOR.



EJEMPLOS COMBINANDO PL/pgSQL con SQL

Una de las características que hacen de este lenguaje una herramienta poderosa, es la
capacidad de interactuar con SQL, obtener datos y procesarlos, la forma de utilizar SQL es tan
familiar que solo basta realizar los comandos SQL para ejecutar o procesar los datos necesarios.

Ejemplo 1 : Buscar un cliente y retornar si existe o no

      CREATE OR REPLACE FUNCTION buscar_cliente (varchar)
      RETURNS bool AS ‘
      DECLARE
         ruc_buscar alias for $1;
         registro clientes%ROWTYPE;
      /* Aqui se define la variable registro del tipo FILA de clientes indicando la tabla, el
      simbolo % y luego la palabra reservada ROWTYPE */
      BEGIN
         SELECT INTO registro * FROM clientes
           WHERE ruc_cliente = ruc_buscar;
          IF FOUND THEN
                RETURN true;
         END IF;
      RETURN false;
      END;
      $$LANGUAGE ‘plpgsql’;


Como podemos apreciar, este codigo busca un cliente en la tabla de clientes, si existe o se
encuentra el cliente, la función devolverá un valor verdadero (true), de lo contrario, ésta
devolverá falso (false).


Ejemplo 2 : Buscar un producto y actualiza su precio segun porcentaje

      CREATE OR REPLACE FUNCTION actualizar_producto (varchar, varchar, real)
      RETURNS bool AS ‘
      DECLARE
         producto ALIAS FOR $1;
         porcentaje ALIAS FOR $2;
       categoria ALIAS FOR $3;
         registro productos%ROWTYPE;
      BEGIN
         SELECT INTO registro * FROM productos
               WHERE productoid = producto;
                                                                                           51
        IF FOUND THEN
           UPDATE productos SET
             PRECIOUNIT = PRECIOUNIT + (PRECIOUNIT * porcentaje)
           WHERE categoriaid = categoria;
           RETURN true;
        END IF;
      RETURN false;
      END;
      $$LANGUAGE ‘plpgsql’;



Este codigo busca el producto, obtiene los datos necesarios y actualiza el registro.



Ejemplo 3 : Incrementar valores según el porcentaje de IVA

      CREATE OR REPLACE FUNCTION inc_iva (integer, real)
      RETURNS integer AS ‘
      DECLARE
        valor ALIAS FOR $1;
        iva ALIAS FOR $2;
        total real;
      BEGIN
        total = valor + (valor * iva);
        RETURN total;
      RETURN;
      END;
      $$LANGUAGE ‘plpgsql’;

Esta funcion no actualiza realmente los datos de la tabla, solamente los procesa parametros,
de tal manera que si los datos ingresados fueran los de una tabla devolvería el valor mas
IVA.


LLAMANDO A LAS FUNCIONES

Hasta ahora hemos descrito a grandes rasgos como construir facilmente funciones de
usuario o procedimientos almacenados, pero todavía no los hemos invocado, a continuación
ejemplos de llamadas a los procedimientos almacenados o funciones en PL/pgSQL :

Este ejemplo retornará true si el cliente
                                                SELECT buscar_
existe o flase si no se encuentra en la tabla
                                                cliente(‘1710185883’);
de clientes
El producto “PRO-0540” actualizará su       SELECT actualizar_producto(‘PRO-
precio en un 3% más.                        0540’,0.03)
Sumará 2 numeros y retornara su valor       SELECT suma(5.45, 10.452)
                                            SELECT valida_ruc (ruc_cliente),
Retornará si el RUT está validado o no pero
                                            nombres, apellidos
analizará todos los registros de la tabla
                                            FROM clientes



52
                                           SELECT
Esta sentencia utiliza la funcion que
                                           codigo,nombre, descripcion,
incrementa los precios con IVA y renombra
                                           inc_iva(precio_venta,0.18) AS precio_
la salida de la funcion inc_iva a “precio_
                                           iva
iva”
                                           FROM productos

Funciones SQL

“Funciones SQL” son construcciones definidas por el standart SQL92, que tiene sintaxis igual que
funciones pero que no pueden ser implementadas como simples funciones.



Funciones           Retorna             Descripcion         Ejemplo
COALESCE(list)      no-NULO              retorna el primer COALESCE(r”le>,
                                        valor no-NULO en
                                        la lista           c2+ 5, 0)

NU-                 inputor NULO         retorna NULO si     NULLIF(c1, ’N/A’)
LLIF(input,value
                    )                   input= value

CASE WHEN expr expr                     retorna la        CASE WHEN c1=
THEN expr[...]                          expresión para la
ELSE exprEND                            primera claúsula 1 THEN ’match’
                                        verdadera         ELSE ’no match’
                                                          END


Funciones Matemáticas

Funciones           Retorna             Descripcion         Ejemplo
dexp(float8)        float8              redimensiona         dexp(2.0)
                                        al exponente
                                        especificado

                    float8              redimensiona         dpow(2.0, 16.0)
                                        un numero
                                        al exponente
                                        especificado
float(int)          float8              convierte un         float(2)
                                        entero a punto
float4(int)         float4              convierte un         float4(2)
                                        entero a punto
integer(float)      int                 convierte un         integer(2.0)
                                        punto flotante a


Funciones String

SQL92 define funciones de texto con sintaxis específica. Algunas son implementadas usando otras
funciones Postgres Los tipos de Texto soportados para SQL92 son char, varchar, y text


                                                                                           53
Funciones            Retorna             Descripcion         Ejemplo
char_                int4                longitud del texto char_
Funciones            Retorna             Descripcion          Ejemplo
charac- ter_         int4                longitud del texto char_
lower(string)        string              convierte el texto lower(’TOM’)
octet_               int4                almacena el          octet_
position(string in   int4                localiza la          position(’o’ in
substring(string     string              extrae un            substring(’Tom’
                     string oth]         borra caracteres     trim(both ’x’ from
upper(text)          text                convierte un texto upper(’tom’)


La mayoría de funciones de texto están disponibles para tipos text, varchar() y char().Algunas son
usadas internamente para implementar las funciones de texto SQL92 descritas arriba.




Funciones            Retorna             Descripcion         Ejemplo
char(text)           char                 convierte un        char(’text string’)
                                         texto a tipo char
char(varchar)        char                 convierte un        char(varchar
                                         varchar a tipo
                                                             ’varchar string’)
Funciones            Retorna             Descripcion          Ejemplo

initcap(text)        text                 primera letra de    initcap(’thomas’)
                                         cada palabra a
lpad(text,int,text) text                  relleno de          lpad(’hi’,4,’??’)
                                         caracteres por
ltrim(text,text)     text                 recorte de
                                         caracteres por la
textpos(text,text) text                   localiza un         position(’high’,’ig’)
                                         subtexto
rpad(text,int,text) text                  relleno de          rpad(’hi’,4,’x’)
                                         caracteres por
rtrim(text,text)     text                 recorte de
                                         caracteres por la
                     text                extrae el subtexto substr(’hi
                                         especificado       there’,3,5)
text(char)           text                 convierte char a    text(’char string’)
                                         tipo text



54
text(varchar)       text                convierte varchar text(varchar
                                        a tipo text       ’varchar string’)
transla-            text                 convierte         translate(’12345’,
te(text,from,to)                        character a string
varchar(char)       varchar              convierte char a    varchar(’char
                                        tipo varchar         string’)
varchar(text)       varchar              convierte text a    varchar(’text
                                        tipo varchar         string’)


La mayoría de funciones explicitamente definidas para texto trabajarán para argumentos char () y
varchar().



Funciones de Fecha/Hora

Las funciones de Fecha/Hora provee un poderoso conjunto de herramientas para manipular varios
tipos Date/Time.

Funciones           Retorna             Descripcion         Ejemplo
                    abstime              convierte a         absti-
                                         abstime


                    timespan            preserva             age(’now’,’1957-
                                        meses y años         06-
                    )
                                                            13’::datetime)
                    datetime            convierte           dateti-
                                        abstime a
                                        datetime

datetime(date)      datetime            convierte date       dateti-
                                        a datetime


                    datetime             convierte           datetime(’1998-
                                         adatetime           02-

                                                            24’::datetime,
da- te_             float8               porción de fecha   da- te_
                                                            part(’dow’,’now’::
                                                            datetime



Funciones           Retorna             Descripcion         Ejemplo
da- te_             float8               porción de hora    date_
                                                            part(’hour’,’4
                                                            hrs 3
                                                            mins’::timespan)

                                                                                           55
da- te_               datetime             fecha truncada        da- te_
                                                                trunc(’month’,’now
                     )

isfinite(abstime)     bool                 un tiempo finito ? isfini-



isfinite(datetime) bool                    una hora finita ?     isfini-



isfinite(timespan) bool                    una hora finita ?     isfinite(’4

reltime(timespan) reltime                  convierte a           reltime(’4
                                           reltime
timespan(reltime) timespan                 convierte a           timespan(’4
                                           timespan


Para las funciones date_part and date_trunc, los argumentos pueden ser ‘year’, ‘month’, ‘day’, ‘hour’,
‘minute’, y ‘second’, asi como las mas especializadas cantidades ‘decade’, ‘century’, ‘millenium’,
‘millisecond’, y ‘microsecond’. date_part permite ‘dow’ para retornar el día de la semana ‘epoch’
para retornar los segundos desde 1970 (para datetime) o ’epoch’ para retornar el total de segundos
transcurridos(para timespan)I

Funciones de Formato

Las funciones de formato proveen un poderoso conjunto de herramientas para convertir varios
datetypes (date/time, int, float, numeric) a texto formateado y convertir de texto formateado a su
datetypes original.



Funciones            Retorna               Descripcion          Ejemplo
 to_                  text                 convierte
char(datetime,                             datetime a string
text)
                                                                to_
 to_                  text                  convierte            to_char( now(),
char(timestamp,                            timestamp a
                                                                ’HH12:MI:SS’)
to_char(int, text) text                     convierte int4/      to_char(125,
                                           int8 a string         ’999’)
to_char(float,        text                  convierte float4/    to_char(125.8,
text)                                      float8 a string
                                                                ’999D9’)
 to_                  text                 convierte             to_char(-125.8,
char(numeric,                              numeric a string
                                                                ’999D99S’)
 to_                  datetime             convierte string      to_datetime(’05
datetime(text,                             a datetime            Dec
text)
                                                                2000 13’, ’DD Mon

56
to_date(text,        date              convierte string    to_date(’05 Dec
text)                                  a date
                                                           2000’, ’DD Mon

 to_                 date              convierte string    to_timestamp(’05
timestamp(text,                        a timestamp
text)                                                      Dec 2000’, ’DD
                                                           Mon
 to_number(text,     numeric            convierte
text)                                  string a
                                       numeric



        Para todas las funciones de formato, el segundo argumento es format-picture.


Format-picture                         Descripción
HH                                     hora del día(01-12)
HH12                                   hora del día(01-12)
MI                                     minuto (00-59)
SS                                     segundos (00-59)
SSSS                                   segundos pasados la medianoche(0-
                                       86399)
Y,YYY                                  año(4 o mas dígitos) con coma
YYYY                                   año(4 o mas dígitos)
YYY                                    últimos 3 dígitos del año
YY                                     últimos 2 dígitos del año
Y                                      último dígito del año
MONTH                                   nombre completo del mes(9-letras) -
                                       todos los caracteres en mayúsculas
Month                                   nombre completo del mes(9-
                                       letras) - el primer carácter en


month                                   nombre completo del mes(9-letras) -
                                       todos los caracteres en minúsculas
MON                                     nombre abreviado del mes(3-letras)
                                       -todos los caracteres en mayúsculas
Mon                                     nombre abreviado del mes(3-letras)
                                       - el primer carácter en mayúsculas
mon                                     nombre abreviado del mes(3-letras)
                                       - todos los caracteres en minúsculas




                                                                                       57
MM    mes (01-12)

DAY    nombre completo del día(9-letters) -
      todos los caracteres en mayúsculas
Day   nombre completo del día(9-letters) -
      el primer carácter en mayúsculas
day    nombre completo del día(9-letters) -
      todos los caracteres en minúsculas
DY     nombre abreviado del día(3-letters)
      - todos los caracteres en mayúsculas
Dy     nombre abreviado del día(3-letters)
      - el primer carácter en mayúsculas
dy     nombre abreviado del día(3-letters)
      - todos los caracteres en minúsculas
DDD   día del año(001-366)

DD    día del mes(01-31)

D     día de la semana(1-7; SUN=1)

W     semana del mes

WW    número de la semana en el año

CC    centuria (2-digits)

J     día juliano(dias desde Enero 1, 4712
      BC)
Q     Quarter

RM     mes en númeral romano(I-XII;
      I=ENE)




58
Joins (Cruces)

El siguiente ejemplo muestra como las joins (cruces) se realizan en SQL.

Para cruzar tres tablas PRODUCTOS, PROVEEDORES y DETALLE ORDENES a través de sus atributos
comunes, formularemos la siguiente instrucción:



                  SELECT pr.nombreprov, p.descripcion, d.cantidad

                  FROM proveedores pr, productos p, detalle_ordenes d

                  WHERE p.productoid = d.productoid AND pr.proveedorid=p.proveedorid;;



En la clausula FROM hemos introducido un alias al nombre para cada relación porque hay atributos
con nombre común en las relaciones. Ahora podemos distinguir entre los atributos con nombre
común simplificando la adicción de un prefijo al nombre del atributo con el nombre del alias seguido
de un punto. La join se calcula de la misma forma, tal como se muestra en Una Inner Join (Una
Join Interna). Primero el producto cartesiano PRODUCTOS x PROVEEDORES x DETALLE ORDENES.
Ahora seleccionamos únicamente aquellas tuplas que satisfagan las condiciones dadas en la clausula
WHERE (es decir, los atributos con nombre común deben ser iguales).



Operadores Agregados

SQL proporciona operadores agregados (como son AVG, COUNT, SUM, MIN, MAX) que toman el
nombre de un atributo como argumento. El valor del operador agregado se calcula sobre todos los
valores de la columna especificada en la tabla completa. Si se especifican grupos en la consulta, el
cálculo se hace sólo sobre los valores de cada grupo (vean la siguiente sección).

Ejemplo:



Si queremos conocer el coste promedio de todos los artículos de la tabla PART, utilizaremos la
siguiente consulta:


       SELECT AVG(preciounit) AS “Precio promedio”

       FROM productos;

Si queremos conocer cuántos artículos se recogen en la tabla PART, utilizaremos la instrucción:


       SELECT COUNT(productoid)

       FROM productos;




                                                                                               59
Agregación por Grupos

SQL nos permite particionar las tuplas de una tabla en grupos. En estas condiciones, los operadores
agregados descritos antes pueden aplicarse a los grupos (es decir, el valor del operador agregado
no se calculan sobre todos los valores de la columna especificada, sino sobre todos los valores de
un grupo. El operador agregado se calcula individualmente para cada grupo).

Ejemplo:

Si queremos conocer cuántos artículos han sido vendidos por cada proveedor formularemos la
consulta:


           SELECT pr.proveedorid, pr.nombreprov, COUNT(p.productoid)

           FROM proveedores pr, productos p

           WHERE pr.proveedorid= p.proveedorid

           GROUP BY pr.proveedorid, pr.nombreprov;

Having

La clausula HAVING trabaja de forma muy parecida a la clausula WHERE, y se utiliza para considerar
sólo aquellos grupos que satisfagan la cualificación dada en la misma. Las expresiones permitidas en
la clausula HAVING deben involucrar funcionen agregadas. Cada expresión que utilice sólo atributos
planos deberá recogerse en la clausula WHERE. Por otro lado, toda expresión que involucre funciones
agregadas debe aparecer en la clausula HAVING.

Ejemplo

Si queremos solamente los proveedores que venden más de un artículo, utilizaremos la consulta:

           SELECT pr.proveedorid, pr.nombreprov, COUNT(p.productoid)

           FROM proveedores pr, productos p

           WHERE pr.proveedorid= p.proveedorid

           GROUP BY pr.proveedorid, pr.nombreprov

          HAVING COUNT(p.productoid) >1;




60
SUBCONSULTAS




               61
En las clausulas WHERE y HAVING se permite el uso de subconsultas (subselects) en cualquier
lugar donde se espere un valor. En este caso, el valor debe derivar de la evaluación previa de la
subconsulta. El uso de subconsultas amplía el poder expresivo de SQL.

Ejemplo:

Si queremos conocer los artículos que tienen mayor precio que el producto llamado

’Tornillos’, utilizaremos la consulta:



       SELECT * FROM productos

       WHERE preciounit > (SELECT preciounit FROM productos

       WHERE descripcion =’BOTON PARA ASADO’);



Cuando revisamos la consulta anterior, podemos ver la palabra clave SELECT dos veces. La primera
al principio de la consulta - a la que nos referiremos como la SELECT externa - y la segunda en
la clausula WHERE, donde empieza una consulta anidada - nos referiremos a ella como la SELECT
interna. Para cada tupla de la SELECT externa, la SELECT interna deberá ser evaluada. Tras cada
evaluación, conoceremos el precio de la tupla llamada ‘BOTON PARA ASADO’, y podremos chequear
si el precio de la tupla actual es mayor.

Si queremos conocer todos los proveedores que no venden ningún producto (por ejemplo, para
poderlos eliminar de la base de datos), utilizaremos:


                         SELECT * FROM proveedores p

                         WHERE NOT EXISTS

                         (SELECT * FROM productos pr WHERE pr.proveedorid =
                          p.proveedorid);


Unión, Intersección, Excepción

Estas operaciones calculan la unión, la intersección y la diferencia de la teoría de conjuntos de las
tuplas derivadas de dos subconsultas.

Ejemplo: Union, Intersect, Except

       La siguiente consulta es un ejemplo de UNION:
           SELECT p.nombreprov, p.contacto

           FROM proveedores p

           WHERE p.nombreprov= ‘TONY’ UNION

           SELECT p.nombreprov, p.contacto

           FROM proveedores p

           WHERE p.nombreprov= ‘JURIS’

62
Aquí se tiene un ejemplo para INTERSECT:
         SELECT p.nombreprov, p.contacto

         FROM proveedores p

         WHERE p.nombreprov= ‘TONY’ INTERSECT

         SELECT p.nombreprov, p.contacto

         FROM proveedores p

         WHERE p.nombreprov= ‘JURIS’

     Finalmente, un ejemplo de EXCEPT:
         SELECT p.nombreprov, p.contacto

         FROM proveedores p

         WHERE p.nombreprov= ‘TONY’ EXCEPT

         SELECT p.nombreprov, p.contacto

         FROM proveedores p

         WHERE p.nombreprov= ‘JURIS’




                                                63
     TRANSACCIONES




64
Las transacciones son un concepto fundamental en todos los sistemas de base de datos, el
punto esencial de una transacción es que engloba múltiples operaciones en un solo paso.
En PostgreSQL las transacciones se configuran simplemente encerrando en un bloque las
operaciones que se desean incluir en la misma, el bloque debe comenzar y terminar con
los comandos BEGIN y COMMIT.

BEGIN

Comienza una transacción en modo encadenado
BEGIN [ WORK | TRANSACTION ]

Para comenzar una transacción de usuario:
BEGIN WORK;

                 Entrada
WORK, TRANSACTION
Palabras clave opcionales. No tienen efecto.

                 Salida
BEGIN
Esto significa que una nueva transacción ha sido comenzada.

NOTICE: BEGIN: already a transaction in progress

Esto indica que una transacción ya está en progreso. La transacción en curso no se ve
afectada.

Descripción
Por defecto, PostgreSQL ejecuta las transacciones en modo no encadenado (también
conocido como “autocommit” en otros sistemas de base de datos). En otras palabras,
cada estado de usuario es ejecutado en su propia transacción y un commit se ejecuta
implícitamente al final del estatuto (si la ejecución fue exitosa, de otro modo se ejecuta
un rollback). BEGIN inicia una transacción de usuario en modo encadenado, todos los
estados de usuarios después de un comando BEGIN se ejecutaran en una transacción
única hasta un explicito COMMIT, ROLLBACK, o aborte la ejecución. Los estados en modo
encadenado se ejecutan mucho más rápido, porque la transacción start/commit requiere
una actividad significativa de CPU y de disco. La ejecución de múltiples estados dentro
de una transacción también es requerida para la consistencia cuando se cambian muchas
tablas relacionadas.

El nivel de aislamiento por defecto de las transacciones en PostgreSQL es READ COMMITTED,
donde las consultas dentro de la transacción solo tiene en cuenta los cambios consolidados
antes de la ejecución de la consulta. Así pues, debes utilizar SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE justo después de BEGIN si necesitas aislamiento de transacciones
más riguroso. Las consultas del tipo SERIALIZABLE solo tendrán en cuenta los cambios
consolidados antes de que la transacción entera comience (realmente, antes de la ejecución
del primer estado DML en una transacción serializable).

Si la transacción está consolidada, PostgresSQL asegurara que todas las actualizaciones
sean hechas o si no que ninguna de ellas lo sea. Las transacciones tienen la propiedad
                                                                                     65
estándar ACID (atómica, consistente, aislada y durable).

COMMIT

Realiza la transacción actual
COMMIT [ WORK | TRANSACTION ]

Para hacer todos los cambios permanentes:
COMMIT WORK;

                 Entrada
WORK, TRANSACTION
        Palabra clave opcional. No tiene efecto.

                 Salida
COMMIT
        Mensaje devuelto si la transacción se realiza con éxito.

NOTICE: COMMIT: no transaction in progress
        Si no hay transacciones en progreso.

Descripción
COMMIT realiza la transacción actual. Todos los cambios realizados por la transacción son
visibles a las otras transacciones, y se garantiza que se conservan si se produce una caída
de la máquina.

ROLLBACK

Interrumpe la transacción en curso
ROLLBACK [ WORK | TRANSACTION ]

Para cancelar todos los cambios:
ROLLBACK WORK;

                 Entrada.
Ninguna.

                 Salida.
ABORT
        Mensaje devuelto si la operación es exitosa.

NOTICE: ROLLBACK: no transaction in progress
        Si no hay transacciones en progreso actualmente.

Descripción
ROLLBACK deshace la transacción actual y provoca que todas las modificaciones originadas
por la misma sean descartadas.
ABORT es un sinónimo de ROLLBACK.
Estructura de una transacción:

B                E                  G               I                  N                  ;
UPDATE cuentas SET balance = balance – 100 WHERE nombre = ‘Alice’;
66
…
COMMIT;

Al momento que se le pasa a postgresql la clausula COMMIT es cuando se escribe en base
de datos las actualizaciones u operación que se desea hacer en la misma, si en algún
momento no queremos hacer COMMIT de alguna operación (quizás se nota que la cuenta
de Alice da un balance negativo) entonces se puede utilizar la clausula ROLLBACK y todas
las actualizaciones dentro de la transacción se cancelaran.

Si no se desea hacer un rollback completo de la transacción, entonces se pueden definir
marcadores (savepoints) hasta los cuales se desea que se regrese en la transacción,
ejemplo:

B                 E                    G                  I                  N             ;
UPDATE cuentas SET balance = balance – 100 WHERE nombre = ‘Alice’;
SAVEPOINT marcador1;
UPDATE cuentas SET balance = balance + 100 WHERE nombre = ‘Bob’;
— se desea descartar la actualizacion para Bob y en vez hacerla para Wally —
ROLLBACK TO marcador1;
UPDATE cuentas SET balance = balance + 100 WHERE nombre = ‘Wally’;
COMMIT;

En el ejemplo anterior se vio el uso de Marcadores y Rollbacks, en este caso lo que paso es
que se realizo una actualizacion sobre la cuenta de Bob, pero a ultima instancia se decide
que el dinero no se le va a abonar a Bob sino a Wally, entonces se realiza un rollback hasta
el marcador llamado marcador1 y se pasa a hacer la actualizacion en la cuenta de Wally.




                                                                                      67
VISTAS Y REGLAS




68
VISTAS
Se puede ver una vista como una tabla virtual, es decir, una tabla que no existe físicamente
en la base de datos, pero aparece al usuario como si existiese. Por contra, cuando hablamos
de una tabla base, hay realmente un equivalente almacenado para cada fila en la tabla en
algún sitio del almacenamiento físico.

Las vistas no tienen datos almacenados propios, distinguibles y físicamente almacenados.
En su lugar, el sistema almacena la definición de la vista (es decir, las reglas para acceder
a las tablas base físicamente almacenadas para materializar la vista) en algún lugar de los
catálogos del sistema (vea System Catalogs).

En SQL se utiliza el comando CREATE VIEW para definir una vista. La sintaxis es:

CREATE VIEW view_name

AS select_stmt

donde select_stmt es una instrucción select válida, como se definió en Select.

Nótese que select_stmt no se ejecuta cuando se crea la vista. Simplemente se almacena
en los catálogos del sistema y se ejecuta cada vez que se realiza una consulta contra la
vista.
SELECT *
FROM categorias
WHERE nombrecat = ‘CARNICOS’;



REGLAS
CREATE RULE - definir una nueva regla de reescritura

Sintaxis:
CREATE [ OR REPLACE ] RULE nombre AS ON evento

  TO tabla [ WHERE condicion ]

  DO [ ALSO | INSTEAD ] { NOTHING | commando | ( commando ; commando ... ) }

Descripción

CREATE RULE define una nueva norma se aplica a una tabla o vista especificada. CREATE
OR REPLACE pronunciarse creará una nueva regla, o cambiará una regla existente del
mismo nombre para la misma tabla.

El sistema de reglas PostgreSQL permite definir una acción alternativa para llevar a
cabo en las inserciones, actualizaciones o supresiones en las tablas de bases de datos.
En términos generales, una regla causas comandos adicionales que se ejecutan cuando
una orden dada en una tabla dada es ejecutada. Las reglas se utilizan también para
implementar las vistas de tabla. Es importante darse cuenta de que una norma es en
realidad un mecanismo de transformación de comandos, o macro de comandos. La
                                                                                        69
transformación ocurre antes que la ejecución de los comandos se inicie.

Parámetros
name :El nombre de una norma para crear. Esta debe ser distinta del nombre de
cualquier otra norma de la misma tabla. Múltiples reglas sobre la misma mesa y mismo
tipo de evento se aplican en el orden del nombre alfabético.

event: El evento es uno de SELECT, INSERT, UPDATE o DELETE.

table: El nombre (opcional esquema cualificada) de la tabla o vista se aplica la regla.

condition: Cualquier expresión condicional SQL (volviendo boolean. La expresión
de condición no puede referirse a las tablas, salvo NEW y OLD, y no puede contener
funciones de agregado.

INSTEAD: indica que los comandos deben ser ejecutados en lugar del comando original.

ALSO: indica que los comandos deben ser ejecutados, además de la orden original.

command: El comando o comandos que componen la acción de la regla. Comandos
válidos son SELECT, INSERT, UPDATE, DELETE o NOTIFY.

Dentro de condición y de comandos, los nombres de tabla especial NEW y OLD pueden
ser usados para referirse a los valores en la tabla referenciada. NUEVO es válido en ON
INSERT y UPDATE ON normas que se refieren a la nueva fila a insertar o actualizar. OLD
es válido en ON y ON UPDATE DELETE normas para hacer referencia a la fila existente se
actualiza o eliminado.

Es muy importante tener cuidado para evitar reglas circulares.

Ejemplos:
CREATE OR REPLACE RULE regla2 AS
  ON INSERT TO empleados
  WHERE empleadoid>3
  DO NOTHING;



CREATE OR REPLACE RULE regla3 AS
  ON INSERT TO empleados
  WHERE apellido like ‘NOBOA’
  DO NOTHING




70
PROCEDIMIENTOS
 ALMACENADOS




                 71
Un procedimiento almacenado se puede definir como un programa, procedimiento ó función,
el cual está almacenado en la base de datos y listo para ser usado.

Existen dos ventajas evidentes al utilizar procedimientos almacenados en nuestro
sistema:

     •	   La ejecución del procedimiento ocurre en el servidor de bases de datos. Esto
          probablemente aumentará el rendimiento de nuestra aplicación al no tenerse que
          mandar datos entre el cliente y el servidor, y no tener que procesar resultados
          intermedios en el cliente para obtener el resultado final.


     •	   Al tener la lógica de la aplicación implementada en la base de datos no tendremos
          que implentarla en los clientes, con el consiguiente ahorro de lineas de código
          redundante y complejidad.

Podemos definir e instalar un procedimiento en PL/pgSQL de la siguiente manera:
          CREATE [ OR REPLACE ] FUNCTION
          nombre_funcion([ [ argmodo ] [ argnombre ] argtipo [, ...] ])
          RETURNS tipo AS $$

          [ DECLARE ]
          [ declaraciones de variables ]

          BEGIN
          codigo

          END;
          $$ LANGUAGE plpgsql
          | IMMUTABLE | STABLE | VOLATILE
          | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
          | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
          | COST execution_cost
          | ROWS result_rows
          | SET configuration_parameter { TO value | = value | FROM CURRENT }


A continuación vamos a ver algunas de las opciones y valores más importantes.

argmodo: El modo de un argumento puede ser IN, OUT, or INOUT. Por defecto se usa IN
si no se define.

argtipo: Los tipos que podemos utilizar son todos los disponibles en PostgreSQL y todos
los definidos por el usuario

declaraciones de variables: Las declaraciones de variables se pueden realizar de la
siguiente manera ($n = orden de declaración del argumento.):
nombre_variable ALIAS FOR $n;
nombre_variable [ CONSTANT ] tipo [ NOT NULL ] [ { DEFAULT | := } expresion ];

72
IMMUTABLE | STABLE | VOLATILE:

IMMUTABLE: Indica que la función no puede alterar a la base de datos y que siempre
devolverá el mismo resultado, dados los mismos valores como argumentos. Este tipo de
funciones no pueden realizar consultas en la base de datos.

STABLE: Indica que la función no puede alterar a la base de datos y que siempre
devolverá el mismo resultado en una consulta individual de una tabla, dados los mismos
valores como argumentos. El resultado podria cambiar entre sentencias SQL.

VOLATILE: Indica que la función puede devolver diferentes valores, incluso dentro de
una consulta individual de una tabla (valor por defecto)

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT:

CALLED ON NULL INPUT: Indica que la función se ejecutará aunque algunos de los
argumentos sean NULL. El usuario tiene la responsabilidad de comprobar si algún
argumento es NULL cuando sea necesario tener esto en cuenta.(valor por defecto)

RETURNS NULL ON NULL INPUT / STRICT: Indican que la función no se ejecutará y
devolverá el valor NULL si alguno de los argumentos es NULL.

SECURITY INVOKER | SECURITY DEFINER:

SECURITY INVOKER: Indica que la función se ejecutará con los privilegios del usuario
que la ejecuta (valor por defecto)

SECURITY DEFINER: Indica que la función se ejecutará con los privilegios del usuario
que la creo.

Ejemplos:

Si queremos que cualquier usuario con acceso a la base de datos pueda usarlo sin tener
que ser el administrador postgres, tendremos que utilizar TRUSTED con el comando
anterior.
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql;
A continuación creamos nuestro primer procedimiento. (Podemos copiar y pegar en el
cliente psql, escribirlo a mano ó usar el editor interno en psql (\e)):
CREATE OR REPLACE FUNCTION ejemplo() RETURNS integer AS $$
BEGIN
 RETURN 104;
END;
$$ LANGUAGE plpgsql;
Ahora definimos la función con un argumento:
CREATE OR REPLACE FUNCTION ejemplo(integer) RETURNS integer AS $$
BEGIN
 RETURN $1;
END;
$$ LANGUAGE plpgsql;
Este procedimiento se podría haber escrito también de las siguientes maneras:
CREATE OR REPLACE FUNCTION ejemplo(numero integer) RETURNS integer AS $$
                                                                                   73
BEGIN
 RETURN numero;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION ejemplo(integer) RETURNS integer AS $$
DECLARE
 numero ALIAS FOR $1;

BEGIN
 RETURN numero;
END;
$$ LANGUAGE plpgsql;
Vamos a empezar a complicar un poco las cosas usando dos argumentos y definiendo
algunas variables:
CREATE OR REPLACE FUNCTION ejemplo(integer, integer) RETURNS integer AS $$
DECLARE
 numero1 ALIAS FOR $1;
 numero2 ALIAS FOR $2;

constante CONSTANT integer := 100;
resultado integer;

BEGIN
 resultado := (numero1 * numero2) + constante;

 RETURN resultado;
END;
$$ LANGUAGE plpgsql;
A continuación vamos a usar una sentencia IF ... THEN en nuestra función:
CREATE OR REPLACE FUNCTION ejemplo_txt(integer, integer) RETURNS text AS $$
DECLARE
 numero1 ALIAS FOR $1;
 numero2 ALIAS FOR $2;

constante CONSTANT integer := 100;
resultado INTEGER;

resultado_txt TEXT DEFAULT ‘El resultado es 104’;

BEGIN
 resultado := (numero1 * numero2) + constante;

IF resultado <> 104 THEN
  resultado_txt := ‘El resultado NO es 104’;
END IF;

 RETURN resultado_txt;
END;
$$ LANGUAGE plpgsql;

74
TRIGGERS




           75
Las diversas funciones proporcionan grandes ventajas al simplificar acciones en la base
de datos. Pero estas acciones requieren la intervención de una persona encargada de
ejecutar dichas funciones cuando se requiera de su actuación. Los Triggers al contrario son
funciones que se ejecutan de forma automática en respuesta a ciertos eventos que ocurren
en la base de datos.

Un trigger es un tipo especial de procedimiento almacenado que es disparado cuando se
intenta modificar los datos que protegen. Un procedimiento almacenado es invocado por el
usuario mientras que un trigger lo es por el SQL.

CREATE TRIGGER crea un nuevo trigger. El trigger se asociará con la tabla especificada y
ejecutará la función especificada function_name cuando ocurra cierto evento.

Los triggers pueden ser definidos a ejecutarse antes o después de cualquier operación
INSERT, UPDATE O DELETE, una vez por fila modificada o por una declaración del SQL. La
función trigger debe ser definida antes de que el propio trigger se cree. Si el trigger se
ejecuta antes del evento, el trigger puede saltar la operación de la fila actual, o cambiar la
fila a ser insertada (solamente para operaciones INSERT y UPDATE). Si el trigger se ejecuta
después del evento, todo cambia, incluyendo los efectos de otros triggers, son visibles al
trigger.

Un trigger que es marcado FOR EACH ROW (row-level) es llamado una vez por cada fila
que modifica la operación. Por ejemplo, una operación DELETE que afecta a 10 filas
causaría cualquier ON DELETE triggers en la relación designada a ser llamada 10 veces
independientemente, una vez por cada fila borrada. En contraste, un trigger que es marcado
FOR EACH STATEMENT (statement-level) sólo se ejecuta una vez para cada operación
dada sin tener en cuenta cuantas filas modifica (en particular, una operación que modifica
cero filas todavía producirá la ejecución de cualquier FOR EACH STATEMENT trigger).
Adicionalmente. Los triggers pueden ser definidos para disparar a un TRUNCATE, aunque
solo FOR EACH STATEMENT.

También, una definición de trigger puede especificar una condición booleana WHEN, la cual
se probará para ver si el trigger se disparará o no. En los triggers de niveles de filas la
condición WHEN puede examinar los valores antiguos y/o nuevos de la columna de la fila.
Los triggers de nivel de sentencias pueden también tener condiciones WHEN, aunque el
rasgo no es tan útil para ellos ya que la condición no puede referirse a cualquier valor en
la tabla.

Si se definen múltiples triggers del mismo tipo para el mismo evento, pueden ser disparados
alfabéticamente de acuerdo al nombre.

SELECT no modifica filas por lo tanto no se pueden crear triggers SELECT. Las reglas y
vistas son más apropiadas en estos casos.

Para crear un trigger en una tabla, el usuario debe tener el privilegio de crear triggers para
esa tabla.

Para eliminar un trigger se debe utilizar la clausula DROP TRIGGER.

Sintaxis:
76
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
  ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE PROCEDURE function_name ( arguments )

Donde:

name: Nombre del Nuevo trigger. Este debe ser distinto de cualquier nombre de otro
tigger en la misma tabla.

BEFORE, AFTER: Determina si la función es llamada antes o después de un evento.

event: Una de INSERT, UPDATE, DELETE o TRUNCATE; este especifica el evento que
ocurrirá cuando el trigger dispare. Se pueden utilizar varios eventos utilizando OR.
Para los triggers UPDATE, es posible especificar una lista de columnas utilizando eta
sintaxis:

                    UPDATE OF column_name1 [, column_name2 ... ]

El trigger disparará solamente si al menos una de las columnas listadas es mencionada
como designada a actualizar.

table: Nombre de la tabla para la cual es el trigger.

FOR EACH ROW, FOR EACH STATEMENT: Especifica si el procedimiento del trigger
será disparado una vez para todas las filas afectadas por el evento, o solo una vez
por cada declaración SQL. Si no se especifica, el valor predeterminado es FOR EACH
STATEMENT.

condition: Una expresión booleana que determina si la función del trigger se ejecutará
relamente. Si WHEN es especificado, la función solo será llamada si la condición retorna
verdadero.

function_name: Función de usuario declarada para no tomar ningún valor y retornar el
tipo de trigger, la cual es ejecutada cuando el trigger dispara.

arguments: Una lista opcional de argumentos separados por coma para ser
proporcionada a la función cuando el trigger es ejecutado. Los argumentos son cadenas
de caracteres constantes. Simples nombres o constantes numéricas pueden ser utilizados
también pero pueden ser convertidos en cadenas de caracteres.

Ejemplos:

Ejecutar la función check_account_update siempre que una fila de la tabla accounts sea
actualizada:

CREATE TRIGGER trigger_actualizacion
  BEFORE UPDATE ON PROVEEDORES
  FOR EACH ROW
  EXECUTE PROCEDURE check_proveedores_update();

                                                                                        77
     IMPLEMENTACIÓN
         DE UNA
      BASE DE DATOS




78
create database PEDIDOS;

CREATE TABLE EMPLEADOS(
      EMPLEADOID int NOT NULL,
      NOMBRE char(30) NULL,
      APELLIDO char(30) NULL,
      FECHA_NAC date NULL,
      REPORTA_A int NULL,
      EXTENSION int NULL,
 CONSTRAINT PK_EMPLEADOS PRIMARY KEY     (EMPLEADOID));


CREATE TABLE PROVEEDORES(
      PROVEEDORID int NOT NULL,
      NOMBREPROV char(50) NOT NULL,
      CONTACTO char(50) NOT NULL,
      CELUPROV char(12) NULL,
      FIJOPROV char(12) NULL,
 CONSTRAINT PK_PROVEEDORES PRIMARY KEY
(PROVEEDORID ) );


CREATE TABLE CATEGORIAS(
      CATEGORIAID int NOT NULL,
      NOMBRECAT char(50) NOT NULL,
 CONSTRAINT PK_CATEGORIAS PRIMARY KEY
(CATEGORIAID) ) ;


CREATE TABLE CLIENTES(
      CLIENTEID int NOT NULL,
      CEDULA_RUC char(10) NOT NULL,
      NOMBRECIA char(30) NOT NULL,
      NOMBRECONTACTO char(50) NOT NULL,
      DIRECCIONCLI char(50) NOT NULL,
      FAX char(12) NULL,
      EMAIL char(50) NULL,
      CELULAR char(12) NULL,
      FIJO char(12) NULL,
 CONSTRAINT PK_CLIENTES PRIMARY KEY
(CLIENTEID) );


CREATE TABLE ORDENES(
      ORDENID int NOT NULL,
      EMPLEADOID int NOT NULL,
      CLIENTEID int NOT NULL,
      FECHAORDEN date NOT NULL,
      DESCUENTO int NULL,
 CONSTRAINT PK_ORDENES PRIMARY KEY
(ORDENID) );



                                                          79
CREATE TABLE DETALLE_ORDENES(
      ORDENID int NOT NULL,
      DETALLEID int NOT NULL,
      PRODUCTOID int NOT NULL,
      CANTIDAD int NOT NULL,
 CONSTRAINT PK_DETALLE_ORDENES PRIMARY KEY
(ORDENID,DETALLEID ) );

CREATE TABLE PRODUCTOS(
      PRODUCTOID int NOT NULL,
      PROVEEDORID int NOT NULL,
      CATEGORIAID int NOT NULL,
      DESCRIPCION char(50) NULL,
      PRECIOUNIT numeric NOT NULL,
      EXISTENCIA int NOT NULL,
 CONSTRAINT PK_PRODUCTOS PRIMARY KEY
(PRODUCTOID )) ;


ALTER TABLE ORDENES
ADD CONSTRAINT FK_ORDENES_CLIEN_ORD_CLIENTES FOREIGN KEY(CLIENTEID)
REFERENCES CLIENTES (CLIENTEID)
on delete restrict on update restrict;

ALTER TABLE ORDENES   ADD CONSTRAINT FK_ORDENES_EMPLE_ORD_EMPLEADO FOREIGN
KEY(EMPLEADOID)
REFERENCES EMPLEADOS (EMPLEADOID)
on delete restrict on update restrict;

ALTER TABLE DETALLE_ORDENES   ADD CONSTRAINT FK_DETALLE__ORDEN_DET_ORDENES FOREIGN
KEY(ORDENID)
REFERENCES ORDENES (ORDENID)
on delete restrict on update restrict;

ALTER TABLE DETALLE_ORDENES ADD CONSTRAINT FK_DETALLE__PROD_DETA_PRODUCTO FOREIGN
KEY(PRODUCTOID)
REFERENCES PRODUCTOS (PRODUCTOID)
on delete restrict on update restrict;


ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTO_CATE_PROD_CATEGORI FOREIGN
KEY(CATEGORIAID)
REFERENCES CATEGORIAS (CATEGORIAID)
on delete restrict on update restrict;

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTO_PROV_PROD_PROVEEDO FOREIGN
KEY(PROVEEDORID)
REFERENCES PROVEEDORES (PROVEEDORID)
on delete restrict on update restrict;

ALTER TABLE EMPLEADOS ADD CONSTRAINT FK_EMPLEADO_REPORTA FOREIGN KEY(REPORTA_A)
REFERENCES EMPLEADOS (EMPLEADOID)
on delete restrict on update restrict;




80
insert   into   categorias    (categoriaid,   nombrecat)   values   (100,   ‘CARNICOS’);
insert   into   categorias    (categoriaid,   nombrecat)   values   (200,   ‘LACTEOS’);
insert   into   categorias    (categoriaid,   nombrecat)   values   (300,   ‘LIMPIEZA’);
insert   into   categorias    (categoriaid,   nombrecat)   values   (400,   ‘HIGINE PERSONAL’);
insert   into   categorias    (categoriaid,   nombrecat)   values   (500,   ‘MEDICINAS’);
insert   into   categorias    (categoriaid,   nombrecat)   values   (600,   ‘COSMETICOS’);
insert   into   categorias    (categoriaid,   nombrecat)   values   (700,   ‘REVISTAS’);



insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(10, ‘DON DIEGO’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(20, ‘PRONACA’, ‘JUAN PEREZ’, ‘0923434467’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(30, ‘TONY’, ‘JORGE BRITO’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(40, ‘MIRAFLORES’, ‘MARIA PAZ’, ‘098124498’,’2458799’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(50, ‘ALMAY’, ‘PEDRO GONZALEZ’, ‘097654567’,’2507190’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(60, ‘REVLON’, ‘MONICA SALAS’, ‘099245678’,’2609876’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(70, ‘YANBAL’, ‘BETY ARIAS’, ‘098124458’,’2450887’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(120, ‘JURIS’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(80, ‘CLEANER’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(90, ‘BAYER’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);
insert into proveedores (proveedorid, nombreprov, contacto,celuprov,fijoprov)               values
(110, ‘PALMOLIVE’, ‘MANUEL ANDRADE’, ‘099234567’,’2124456’);



INSERT   INTO   PRODUCTOS    VALUES   (1,10,100,’SALCHICHAS VIENESAS’,2.60,200);
INSERT   INTO   PRODUCTOS    VALUES   (2,10,100,’SALAMI DE AJO’,3.60,300);
INSERT   INTO   PRODUCTOS    VALUES   (3,10,100,’BOTON PARA ASADO’,4.70,400);
INSERT   INTO   PRODUCTOS    VALUES   (4,20,100,’SALCHICHAS DE POLLO’,2.90,200);
INSERT   INTO   PRODUCTOS    VALUES   (5,20,100,’JAMON DE POLLO’,2.80,100);
INSERT   INTO   PRODUCTOS    VALUES   (6,30,200,’YOGURT NATURAL’,4.30,80);
INSERT   INTO   PRODUCTOS    VALUES   (7,30,200,’LECHE CHOCOLATE’,1.60,90);
INSERT   INTO   PRODUCTOS    VALUES   (8,40,200,’YOGURT DE SABORES’,1.60,200);
INSERT   INTO   PRODUCTOS    VALUES   (9,40,200,’CREMA DE LECHE’,3.60,30);
INSERT   INTO   PRODUCTOS    VALUES   (10,50,600,’BASE DE MAQUILLAJE’,14.70,40);
INSERT   INTO   PRODUCTOS    VALUES   (11,50,600,’RIMMEL’,12.90,20);
INSERT   INTO   PRODUCTOS    VALUES   (13,60,600,’SOMBRA DE OJOS’,9.80,100);

set datestyle to dmy;

INSERT   INTO   EMPLEADOS    VALUES   (1,’JUAN’, ‘CRUZ’, ‘18/01/67’,null, 231);
INSERT   INTO   EMPLEADOS    VALUES   (2,’MARIO’, ‘SANCHEZ’, ‘01/03/79’,1,144);
INSERT   INTO   EMPLEADOS    VALUES   (3,’VERONICA’, ‘ARIAS’, ‘23/06/77’,1, 234);
INSERT   INTO   EMPLEADOS    VALUES   (4,’PABLO’, ‘CELY’, ‘28/01/77’,2, 567);
INSERT   INTO   EMPLEADOS    VALUES   (5,’DIEGO’, ‘ANDRADE’, ‘15/05/70’,2, 890);
INSERT   INTO   EMPLEADOS    VALUES   (6,’JUAN’, ‘ANDRADE’, ‘17/11/76’,3, 230);
                                                                                              81
INSERT INTO EMPLEADOS VALUES (7,’MARIA’, ‘NOBOA’, ‘21/12/79’,3, 261);

INSERT INTO CLIENTES VALUES (1,’1890786576’,’SUPERMERCADO ESTRELLA’,’JUAN
ALBAN’,’AV.AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (2,’1298765477’,’EL ROSADO’,’MARIA CORDERO’,’AV.AEL
INCA’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (3,’1009876567’,’DISTRIBUIDORA PRENSA’,’PEDRO
PINTO’,’EL PINAR’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (4,’1876090006’,’SU TIENDA’,’PABLO PONCE’,’AV.
AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (5,’1893456776’,’SUPERMERCADO DORADO’,’LORENA
PAZ’,’AV.6 DICIEMBRE’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (6,’1678999891’,’MI COMISARIATO’,’ROSARIO UTRERAS’,’AV.
AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (7,’1244567888’,’SUPERMERCADO DESCUENTO’,’LETICIA
ORTEGA’,’AV.LA PRENSA’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (8,’1456799022’,’EL DESCUENTO’,’JUAN TORRES’,’AV.
PATRIA’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (9,’1845677777’,’DE LUISE’,’JORGE PARRA’,’AV.
AMAZONAS’,NULL,NULL,NULL,NULL);
INSERT INTO CLIENTES VALUES (10,’183445667’,’YARBANTRELLA’,’PABLO POLIT’,’AV.REPUBL
ICA’,NULL,NULL,NULL,NULL);

INSERT   INTO   ORDENES   VALUES(1,3,4,’17/06/07’, 5);
INSERT   INTO   ORDENES   VALUES(2,3,4,’02/06/07’, 10);
INSERT   INTO   ORDENES   VALUES(3,4,5,’05/06/07’, 6);
INSERT   INTO   ORDENES   VALUES(4,2,6,’06/06/07’, 2);
INSERT   INTO   ORDENES   VALUES(5,2,7,’09/06/07’, NULL);
INSERT   INTO   ORDENES   VALUES(6,4,5,’12/06/07’, 10);
INSERT   INTO   ORDENES   VALUES(7,2,5,’14/06/07’, 10);
INSERT   INTO   ORDENES   VALUES(8,3,2,’13/06/07’, 10);
INSERT   INTO   ORDENES   VALUES(9,3,2,’17/06/07’, 3);
INSERT   INTO   ORDENES   VALUES(10,2,2,’18/06/07’, 2);

INSERT   INTO    detalle_ordenes   VALUES(1,1,1,2);
INSERT   INTO    detalle_ordenes   VALUES(1,2,4,1);
INSERT   INTO    detalle_ordenes   VALUES(1,3,6,1);
INSERT   INTO    detalle_ordenes   VALUES(1,4,9,1);

INSERT   INTO    detalle_ordenes   VALUES(2,1,10,10);
INSERT   INTO    detalle_ordenes   VALUES(2,2,13,20);
INSERT   INTO    detalle_ordenes   VALUES(3,1,3,10);
INSERT   INTO    detalle_ordenes   VALUES(4,1,9,12);

INSERT   INTO    detalle_ordenes   VALUES(5,1,1,14);
INSERT   INTO    detalle_ordenes   VALUES(5,2,4,20);
INSERT   INTO    detalle_ordenes   VALUES(6,1,3,12);
INSERT   INTO    detalle_ordenes   VALUES(7,1,11,10);
INSERT   INTO    detalle_ordenes   VALUES(8,1,2,10);
INSERT   INTO    detalle_ordenes   VALUES(8,2,5,14);
INSERT   INTO    detalle_ordenes   VALUES(8,3,7,10);
INSERT   INTO    detalle_ordenes   VALUES(9,1,11,10);
INSERT   INTO    detalle_ordenes   VALUES(10,1,1,5);




82
 EJERCICIOS
PROPUESTOS




              83
/************************************************************************
                            EJERCICIOS
************************************************************************/
/*
1. Actualizar el precio unitario de los productos de la categoría CARNICOS,
subiéndolos en un 10%
*/
select * from productos;
update productos
set preciounit= (preciounit*0.1) + preciounit;

/*
2. Actualizar el teléfono celular del proveedor cuyo contacto es MANUEL ANDRADE,
con el valor 099010291
*/

select * from proveedores;
update proveedores
set celuprov= ‘0990101291’
where contacto= ‘MANUEL ANDRADE’;
/*
3. Borrar el producto YOGURT DE SABORES*/

select * from productos;
delete from productos
where descripcion =’YOGURT DE SABORES’;

/*
4.   Realizar las siguientes consultas:

4.1 Mostrar todas las órdenes: el id de la orden, el apellido y nombre del empleado
que la atendió
    el nombre de la compañía cliente y la fecha de orden
*/

SELECT * FROM ORDENES;
select * from empleados;
select * from clientes;

select ord.ordenid, emp.empleadoid, emp.nombre, emp.apellido, ord.fechaorden, cli.
clienteid, cli.nombrecia from ordenes ord, empleados emp, clientes cli
where ord.empleadoid=emp.empleadoid and ord.clienteid=cli.clienteid ;

/*
4.2 Mostrar la suma total de cada tipo de producto pedidos en todas las órdenes.
*/

select * from detalle_ordenes;
select productoid, sum (cantidad) as “Suma total” from detalle_ordenes
group by productoid;

/*
4.3 Mostrar el número de órdenes atendidas por cada empleado, incluidos los que
tienen 0 órdenes.
84
*/
select * from empleados;
select * from ordenes;

select nombre as “Nombre” , apellido as “Apellido”, count (ord.empleadoid) as “# de
ordenes” from empleados emp left join ordenes ord
on emp.empleadoid= ord.empleadoid
group by nombre, apellido;

/*
4.4 Muestre los proveedores y la suma de dinero vendido en los productos de ese
proveedor.
*/
select * from proveedores;
select * from productos;
select * from detalle_ordenes;

select prov.nombreprov as “Nombre”,sum(d.cantidad * prod.preciounit ) as “Suma
dinero” from proveedores prov, productos prod, detalle_ordenes d
where d.productoid= prod.productoid and prov.proveedorid= prod.proveedorid
group by nombreprov;
/*
5. Realizar el siguiente procedimiento almacenado.

5.1 Escriba un procedimiento almacenado que reciba como parámetro un código de
proveedor y
    devuelve el número de órdenes en las que están incluidos productos de ese
proveedor.
*/

select * from productos;
select * from ordenes;
select * from detalle_ordenes;

create or replace function num_ordene_proveedor(idproo int) returns integer as $$
declare
num int;
begin
select count(p.productoid) into num from productos p join detalle_ordenes dor
on (p.productoid=dor.productoid)join ordenes ord on(ord.ordenid=dor.ordenid)
where proveedorid=idproo
group by proveedorid;
return num;
end;
$$LANGUAGE ‘plpgsql’;
select num_ordene_proveedor(10)

/*
5.2 Escriba un procedimiento almacenado que reciba como parámetro un nombre de una
categoría y
    devuelve el código del producto de esa categoría que tiene más unidades
vendidas.
*/
select * from categorias;
select * from productos;
select * from detalle_ordenes;
                                                                                  85
create or replace function mas_vendido (nomCat character(50)) returns integer as $$
declare
num int;
begin
select p.productoid,sum(cantidad)INTO NUM from productos p join categorias c on
(c.categoriaid=p.categoriaid)
join detalle_ordenes dor on (p.productoid=dor.productoid)
where c.nombrecat=nomCat
group by descripcion, p.productoid
order by sum DESC
limit 1;
return num;
end;
$$LANGUAGE ‘plpgsql’;
select mas_vendido(‘CARNICOS’);




86
         BIBLIOGRAFÍA
   	 http://www.dataprix.com/1-caracteristicas-postgresql
   	 http://www.postgresql.org/
   	 http://postgresql.org.pe/articles/introduccion_a_postgresql.pdf
   	 http://www.htmlpoint.com/sql/sql_07.htm
   	 http://structio.sourceforge.net/guias/servidor_OpenBSD/postgresql.html
   	 http://pacodebian.iespana.es/postgres.pdf
   	 http://www.eaprende.com/gestor-de-basededatos-mysql-postresql-sqlite.html
   	 http://hpfloresj.blogspot.com/2008/09/como-crear-una-base-de-datos-en.html
   	 http://postgresql.org.pe/articles/introduccion_a_postgresql.pdf
   	 http://structio.sourceforge.net/guias/servidor_OpenBSD/postgresql.html
   	 http://www.eaprende.com/gestor-de-basededatos-mysql-postresql-sqlite.html
   	 http://hpfloresj.blogspot.com/2008/09/como-crear-una-base-de-datos-en.html
   	 http://elblogdeover.wordpress.com/2008/01/15/transacciones-en-postgresql/
   	 http://www.ibiblio.org/pub/linux/docs/LuCaS/Postgresql-
              es/web/navegable/todopostgresql/sql-begin.html
	    http://www.ibiblio.org/pub/linux/docs/LuCaS/Postgresql-
      es/web/navegable/todopostgresql/sql-commit.html#SQL-COMMIT-TITLE
   	 http://sdi.bcn.cl/desarrollo/doctos/PL_pgSQL.pdf
   	 http://mygnet.net/manuales/postgresql/vistas_y_funciones_postgres.1245
   	 http://mmc.geofisica.unam.mx/LuCAS/Tutoriales/NOTAS-CURSO-BBDD/notas-
              curso-BD/node134.html
   	 http://www.e-socrates.org/mod/resource/view.php?id=6549
           ESCUELA POLITÉCNICA NACIONAL

INGENIERIA EN SISTEMAS INFORMÁTICOS Y DE COMPUTACIÓN

                        2010

								
To top