ESCUELA POLITCNICA

NACIONAL

FACULTAD DE INGENIERA DE SISTEMAS

INGENIERA EN SISTEMAS INFORMTICOS Y DE COMPUTACIN

BASES DE DATOS

PERIODO ACADMICO 2014-A

BREVE RESUMEN DE CONSULTAS CON

POSTGRESQL

QUITO, Mayo del 2014

CONSULTAS CON POSTGRESQL

PostgreSQL es un gestor de base de datos relacional, es basado en el lenguaje de

consulta SQL por lo tanto sus consultas se las hace de la misma manera que en SQL

Server.

En PostgreSQL se puede hacer la administracin mediante SQL Shell, que es mediante

lneas de consola o mediante una herramienta grfica denominada pgAdmin III.

Ventana principal de pgAdmin III

La administracin de bases de datos con la interfaz grfica se la ver ms adelante.

En una instalacin por defecto de PostgreSQL podremos tener disponibles los siguientes lenguajes: PL/pgSQL, PL/Perl, PL/Tcl y PL/Python.

Se va a utilizar el lenguaje de procedimientos PL/pgSQL por ser el que se tiene seguro disponible. PL/pgSQL es muy parecido al lenguaje PL/SQL utilizado por Oracle.

Los objetivos de PL/pgSQL cuando se creo fueron:

Poder ser usado para crear funciones y disparadores (triggers) Aadir estructuras de control al lenguaje SQL Poder realizar clculos complejos Heredar todos los tipos, funciones y operadores definidos por el usuario Poder ser definido como un lenguaje "de confianza" Fcil de usar

Ahora se har uso de SQl Shell, o tambin conocido como psql

Al inicio nos pide el ingreso al servidor que se va a hacer uso, como en la demostracin

slo se la har de manera local, se da enter sin ingresar nada.

Luego pide el nombre de la base de datos a la cual se va a ingresar, como an no se ha

creado niguna, entramos a la base que se instala por defecto, simplemente se da enter.

Se hace uso del puerto predeterminado y en el username que por defecto yo lo he dejado

como postgres, simplemente se da enter en las 2 opciones.

Luego pide la contrasea de usuario, en este caso vendra a ser la que se puso en el

momento de la instalacin, se introduce la clave especificada y se da enter.

Se puede apreciar que se est usando la base postgres.

CREACIN DE UNA BASE DE DATOS

Para la creacin de base de datos se usa la sentencia CREATE DATABASE nombreDB;

es importante el ; al final, sino no acepta la sentencia.

Para el borrado de la misma se hace uso de DROP DATABASE nombreDB;

Para hacer uso de la base de datos creada anteriormente se abre una nueva consola, en

donde en Database se pone el nombre mydb y se ingresa con la misma contrasea.

Se puede apreciar la versin instalada, en este caso es la ltima en este momento (9.3.4).

Adems e puede ver la fecha en la cual se hace el uso de la misma

Muchos comandos en el prompt pueden ser tiles, estos se los puede obtener con la

sentencia \?.

Existen muchas sentencias, pero no es necesario por el momento usarlas, por lo que no

se las detallar.

CONCEPTOS

La nocin fundamental en Postgres es la de clase, que es una coleccin de instancias de

un objeto. Cada instancia tiene la misma coleccin de atributos y cada atributo es de un

tipo especfico. Ms an, cada instancia tiene un identificador de objeto (OID)

permanente, que es nico a lo largo de toda la instalacin. Ya que la sintaxis SQL hace

referencia a tablas, usaremos los trminos tabla y clase indistintamente. Asimismo una

filaSQL es una instancia y las columnas SQL son atributos. Como ya se dijo

anteriormente, las clases se agrupan en bases de datos y una coleccin de bases de

datos gestionada por un nico proceso postmaster constituye una instalacin o sitio.

CREACIN DE UNA TABLA

Se proceder a crear dos tablas como ejemplo para la demostracin.

Se hace uso de la sentencia CREATE TABLE nombre tabla

Se debe tomar en cuenta que las palabras clave y los identificadores son sensibles a las

maysculas y minsculas. Los identificadores pueden llegar a ser sensibles a maysculas

o minsculas si se les pone entre dobles comillas, tal como lo permite SQL92. Postgres

SQL soporta los tipos habituales de SQL como: int, float, real, smallint, char(N),

varchar(N), date, time, y timestamp, as como otros de tipo general y otros con un rico

conjunto de tipos geomtricos.

**Para el borrado de una tabla se hace uso de la sentencia DROP TABLE nombreTabla;

INSERCIN DE DATOS:

Se hace uso de la sentencia INSERT INTO nombreTabla VALUES (), como se muestra a

continuacin:

Otros ejemplos de insercin:

CONSULTAS A LA BASE DE DATOS:

Las clases pueden ser consultadas mediante la sentencia SELECT, su sintaxis es:

SELECT [ALL|DISTINCT] { * | expr_1 [AS c_alias_1] [, ... [, expr_k [AS c_alias_k]]]} FROM table_name_1 [t_alias_1] [, ... [, table_name_n [t_alias_n]]] [WHERE condition] [GROUP BY name_of_attr_i [,... [, name_of_attr_j]] [HAVING condition]] [{UNION [ALL] | INTERSECT | EXCEPT} SELECT ...] [ORDER BY name_of_attr_i [ASC|DESC] [, ... [, name_of_attr_j [ASC|DESC]]]];

Consultas sencillas:

SELECT * FROM nombreTabla, para seleccionar todos los campos de la tabla

Se pueden seleccionar campos calculados:

Con la palabra clave AS se puede renombrar el nombre de columnas.

Los operadores booleanos (and, or and not) ) se pueden usar en la cualificacin de

cualquier consulta. Por ejemplo,

Los operadores de comparacin son los siguientes:

Ejemplos con los operadores:

PATRONES DE BSQUEDA

USO DE LA SENTENCIA ILIKE:

Esta sentencia no distingue maysculas de minsculas.

USANDO ORDER BY:

Se puede especificar que los resultados de un select puedan ser devueltos de manera

ordenada o quitando las instancias duplicadas.

Por defecto se ordena ascendentemente, para especificar de manera descendente se usa

DESC.

Cualquier consulta select puede ser redireccionada a una nueva clase:

Esto forma de manera implcita un comando create, crendose una nueva clase temp con

el atributo names y types especificados en la lista destino del comando select into.

Entonces podremos , por supuesto, realizar cualquier operacin sobre la clase resultante

como lo haramos sobre cualquier otra clase.

ACTUALIZACIN DE DATOS:

BORRADO DE DATOS:

DELETE FROM classname;

Sin una cualificacin, delete simplemente borrar todas las instancias de la clase

dada, dejndola vaca. El sistema no pedir confirmacin antes de hacer esto.

USO DE FUNCIONES

Como otros lenguajes de consulta, PostgreSQL soporta funciones de conjunto. Una funcin de conjunto calcula un nico resultado a partir de mltiples filas de entrada. Por ejemplo, existen funciones globales para calcular count(contar) ,sum (sumar), avg (media), max (mximo) and min (mnimo) sobre un conjunto de instancias.

Es importante comprender la relacin entre las funciones de conjunto y las clusulas SQL where y having . . La diferencia fundamental entre where y having es que: where selecciona las columnas de entrada antes de los grupos y entonces se computan las funciones de conjunto (de este modo controla qu filas van a la funcin de conjunto), mientras que having selecciona grupos de filas despus de los grupos y entonces se computan las funciones de conjunto. De este modo la clusula where puede no contener funciones de conjunto puesto que no tiene sentido intentar usar una funcin de conjunto para determinar qu fila ser la entrada de la funcin. Por otra parte, las clusulas having siempre contienen funciones de conjunto. (Estrictamente hablando, usted puede escribir una clusula havingque no use funciones de grupo, pero no merece la pena. La misma condicin podra ser usada de un modo ms eficaz con where).

Las funciones de grupo son tambin muy tiles combinndolas con clusulas group by. Por ejemplo, podemos obtener la temperatura mnima tomada en cada ciudad con:

Podemos filtrar estas filas agrupadas usando having. Adems si slo nos interesan las ciudades cuyos nombres empiecen por 'Q', deberamos hacer :

MANIPULACIN DE CASE

Funciones que modifican la Mayscula o minscula.

Funciones de manipulacin de caracteres:

Funciones numricas:

TRABAJANDO CON FECHAS:

PostgreSQL guarda internamente las fechas bajo el siguiente formato: Siglo, Ao, Mes, Da, Horas, Minutos y Segundos.

Las fechas por defecto se muestran con el formato YYYY-MM-DD.

Funciones que retornan la Fecha y hora:

Operaciones aritmticas con fechas:

Operaciones aritmticas con horas:

CONVERSIONES

Implcitas: Realizadas por el sistema.

Explcitas: Cuando el usuario debe hacerlas.

USANDO CAST

CONVERSIONES EXPLCITAS

Usando TO_CHAR:

Usando TO_CHAR con fechas:

RECIBIENDO DATOS DE DIFERENTES TABLAS

Se hace uso del lgebra relacional tal como se define en SQL.

Se tiene las siguientes tablas:

Unin

Interseccin

Diferencia

Se puede ver que la diferencia no es conmutativa.

OPERACIONES CON JOINS

Producto Cartesiano

CROOS JOIN es equivalente a INNER JOIN ON (true) esto porque se le debe aplicar una

condicin para acotar el resultado.

Equi-Join

Usando where:

Natural Join

Lo mismo que el Equi-Join pero sin repetir columnas iguales.

Left Outer Join, Right Outer Join, Full Outer Join

no es necesario poner el Outer.

VISTAS

La creacin de vistas se la hace de la misma manera que define el lenguaje SQL.

TRANSACCIONES

Definimos transaccin como un conjunto de operaciones que tienen significado solamente al actuar juntas. PostgreSQL ofrece soporte a transaccin es, garantizando que ambas operaciones se realicen o que no se realice ninguna. Para iniciar una transaccin, se utiliza el comando begin y para finalizarla, commit.

Al cerrar la transaccin, los registros insertados ya son visibles para todos los usuarios. Si por alguna razn, por ejemplo una cada del sistema, no se ejecuta el commit, la transaccin se cancela. La forma explcita de cancelar una transaccin es con el comando rollback.

Con Rollback las filas insertadas dentro de la transaccin no se guardan.

PROCEDIMIENTOS ALMACENADOS Un procedimiento almacenado en PostgreSQL se puede escribir en mltiples lenguajes de programacin.

PL/pgSQL es un lenguaje estructurado en bloques. Como mnimo se tendr un bloque principal en el procedimiento almacenado y dentro de este se podr tener sub-bloques. Un bloque se define de la siguiente manera (Todo entre los corchetes [] es opcional):

[ > ] [ DECLARE declaraciones de variables ] BEGIN codigo END [ etiqueta ];

A continuacin se va a ver algunas de las opciones y valores ms 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 declaracin del argumento.):

nombre_variable ALIAS FOR $n; nombre_variable [ CONSTANT ] tipo [ NOT NULL ] [ { DEFAULT | := } expresion ];

IMMUTABLE | STABLE | VOLATILE:

IMMUTABLE: Indica que la funcin 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 funcin 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 funcin 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 funcin se ejecutar aunque algunos de los argumentos sean NULL. El usuario tiene la responsabilidad de comprobar si algn argumento es NULL cuando sea necesario tener esto en cuenta.(valor por defecto)

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

SECURITY INVOKER | SECURITY DEFINER:

SECURITY INVOKER: Indica que la funcin se ejecutar con los privilegios del usuario que la ejecuta (valor por defecto)

SECURITY DEFINER: Indica que la funcin se ejecutar con los privilegios del usuario que la creo.

NOTA: La figura de PROCEDURE no existe en postgre (version libre) como si lo existe en ORACLE o SQL SERVER. En su defecto PostgreSQL tiene FUNCTION, estas funciones pueden o no devolver un valor, eso depender de lo que se necesite hacer, segn como se la defina.

Por otro lado, no es como lenguaje C, en realidad PL/PGSQL guarda mucho los estndares de SQL, es bastante similar a Oracle y con transact-SQL slo cambian algunas sentencias y nada ms.

Para hacer uso de esto se crear una nueva base de datos:

postgres=# CREATE DATABASE test001; CREATE DATABASE

Se debe instalar el lenguaje a usar

Puede como en este caso ya existir, sino se instalar.

Se crea el procedimiento:

Y se lo puede usar:

Tambin se lo puede crear con argumentos de entrada, y declarar un valor de tipo integer:

Tambin se la puede definir con dos argumentos de entrada y realizar una operacin con ellos:

Se lo usa de la siguiente manera:

Usando sentencias IF-ELSE

Uso de la funcin:

Solo queda decir que en la definicin de un procedimiento no solo se tiene en cuenta el nombre del mismo para diferenciarlo de otros, los argumentos de la funcin tambien se tienen en cuenta. ejemplo(), ejemplo(integer), ejemplo(integer, integer) y ejemplo(text) son todos procedimientos diferentes, aunque se llamen igual.

En psql existe un comando muy bueno que nos ensea como una funcin est definida en la base de datos.

De ah se pueden complicar ms los procedimientos, pero en esta ocasin slo se ver hasta ah para ver su funcionamiento.

TRIGGERS Los triggers se ejecutan mediante los comandos insert, delete y uptade.

Variables especiales en PL/pgSQL

Cuando una funcin escrita en PL/pgSQL es llamada por un disparador tenemos ciertas variables especiales disponibles en dicha funcin. Estas variables son las siguientes:

NEW Tipo de dato RECORD; Variable que contiene la nueva fila de la tabla para las operaciones INSERT/UPDATE en disparadores del tipo row-level. Esta variable es NULL en disparadores del tipo statement-level.

OLD Tipo de dato RECORD; Variable que contiene la antigua fila de la tabla para las operaciones UPDATE/DELETE en disparadores del tipo row-level. Esta variable es NULL en disparadores del tipo statement-level.

TG_NAME Tipo de dato name; variable que contiene el nombre del disparador que est usando la funcin actualmente.

TG_WHEN Tipo de dato text; una cadena de texto con el valor BEFORE o AFTER dependiendo de como el disparador que est usando la funcin actualmente ha sido definido

TG_LEVEL Tipo de dato text; una cadena de texto con el valor ROW o STATEMENT dependiendo de como el disparador que est usando la funcin actualmente ha sido definido

TG_OP Tipo de dato text; una cadena de texto con el valor INSERT, UPDATE o DELETE dependiendo de la operacin que ha activado el disparador que est usando la funcin actualmente.

TG_RELID Tipo de dato oid; el identificador de objeto de la tabla que ha activado el disparador que est usando la funcin actualmente.

TG_RELNAME Tipo de dato name; el nombre de la tabla que ha activado el disparador que est usando la funcin actualmente. Esta variable es obsoleta y puede desaparacer en el futuro. Usar TG_TABLE_NAME.

TG_TABLE_NAME Tipo de dato name; el nombre de la tabla que ha activado el disparador que est usando la funcin actualmente.

TG_TABLE_SCHEMA Tipo de dato name; el nombre de la schema de la tabla que ha activado el disparador que est usando la funcin actualmente.

TG_NARGS Tipo de dato integer; el nmero de argumentos dados al procedimiento en la sentencia CREATE TRIGGER.

TG_ARGV[] Tipo de dato text array; los argumentos de la sentencia CREATE TRIGGER. El ndice empieza a contar desde 0. Indices invlidos (menores que 0 mayores/iguales que tg_nargs) resultan en valores nulos.

Se crea la tabla historial, para guardar ah las filas que se van borrando de la tabla weather:

.

Para poder utilizar una funcin como disparador, no debe recibir argumentos y debe retornar el tipo especial trigger:

Ahora se procede a crear el trigger:

La definicin del trigger se la puede observar de la siguiente manera:

Ahora se probar su funcionamiento: Se aprecia que la tabla historial est vaca:

Al intentar borrar un registro se aprecia que o se lo puede hacer, sin embargo el dato que se ha querido borrar ya se ha almacenado en la tabla especificada.

Ahora se crear un trigger para INSERT Y UPDATE:

Visualizamos el trigger en la tabla:

Probando su funcionamiento:

Pero esto no es muy efectivo, por lo tanto se borran los triggers anteriores para crear uno solo para INSERT, UPDATE y DELETE.

Ahora vemos el trigger en la tabla:

Probamos el funcionamiento con las tres sentencias:

REFERENCIAS:

http://www.postgresql.org.es/principal

http://www.postgresql.org/docs/9.3/interactive/index.html

http://www.postgresql.org/docs/9.3/interactive/tutorial-table.html

http://www.postgresql.org.es/node/297

http://www.postgresql.org.es/node/301

http://ocw.uoc.edu/computer-science-technology-and-multimedia/bases-de-datos/bases-de-datos/P06_M2109_02152.pdf

http://www.slideshare.net/nicola51980/postgresql-leccin-1-usando-la-sentencia-select