Postgresql

¿Qué es PostGreSQL?

Es un sistema de gestión de bases de datos objeto-relacional (ORDBMS) basado en el proyecto POSTGRES. Ya que es una derivación libre (OpenSource) de este proyecto, y utiliza el lenguaje SQL92/SQL99

Un poco de historia

*        Se inicia en la Universidad de Berkeley en 1977 bajo el nombre Ingres como un ejercicio de aplicación de las teorías de las RDBMS.

*         1986, cambia su nombre a Postgres con el objetivo de aplicar los conceptos de Objetos Relacionales.

*         1995, cambia su nombre a Postgres95 que luego derivaría a PostgreSQL

*         1996, el proyecto se integra al mundo del Open Source inicia en la versión 6.0

*         2004, PostgreSQL 8.0, adopción en el mundo comercial, se le califico como la 5ta DBMS mas popular en USA.

*         2005 Julio, PostgreSQL paso el test de Coverty Inspected encontrando solo 20 errores en 775,000 lineas de código.

*        Actuamente version 9.1

PostGreSQL y la orientación a objetos

El argumento a favor de las bases de datos objeto-relacionales sostiene que permite realizar una migración gradual de sistemas relacionales a los orientados y, en algunas circunstancias, coexistir ambos tipos de aplicaciones durante algún tiempo.

Características

*        Soporta distintos tipos de datos:

Ø  tipos base, fecha, monetarios, elementos gráficos, datos sobre redes (MAC, IP ...), cadenas de bits, etc.  También permite la creación de tipos propios.

*        Incorpora una estructura de datos array.

*        Soporte nativo para los lenguajes mas populares del medio : PHP, C, C++, Perl, Python, etc.

*        Incorpora funciones de diversa índole:

Ø  manejo de fechas, geométricas, orientadas a operaciones con redes, etc.

*        Permite la declaración de funciones propias, así como la definición de disparadores.

*        Soporta el uso de índices, reglas y vistas.

*        Incluye herencia entre tablas (aunque no entre objetos, ya que no existen), por lo que a este gestor de bases de datos se le incluye entre los gestores objeto-relacionales.

*        Permite la gestión de diferentes usuarios, como también los permisos asignados a cada uno de ellos.

*        PostGreSQL puede funcionar en múltiples plataformas (en general, en todas las modernas basadas en Unix) Linux, BSDs, Mac OS, Beos, Windows, etc.

*        Se distribuye bajo licencia BSD, lo que permite su uso, redistribución, modificación con la única restricción de mantener el copyright del software a sus autores, en concreto el PostGreSQL Global Development Group y la Universidad de California.

Sentencia insert, update y delete

Insert

INSERT INTO table_name (name_of_attr_1

[, name_of_attr_2 [,...]])

VALUES (val_attr_1

[, val_attr_2 [, ...]]);

Ejemplo:

INSERT INTO "Venta"("ID_Cliente", "ID_Producto", "Cantidad", "Total", "Fecha") VALUES ( 1,1 ,2 ,30 ,'15/03/2012');

Update

UPDATE table_name

SET name_of_attr_1 = value_1

[, ... [, name_of_attr_k = value_k]]

WHERE condition;

Ejemplo:

UPDATE “Producto“ SET “Costo_Unitario”=10 WHERE "ID_Producto " = 3 ;

Delete

DELETE FROM table_name

WHERE condition;

Ejemplo:

DELETE FROM “Producto“ WHERE "ID_Producto " = 3 ;

Select

El comando más usado en SQL es la instrucción SELECT, que se utiliza para recuperar datos. La 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]]]];

Ejemplos:

SELECT *FROM "Producto";

SELECT "Descripcion" FROM "Producto" WHERE "Precio_Unitario" >'10';

Subconsulta

Ejemplo:

SELECT "Nombre“ AS  "NOMBRE DEL CLIENTE" FROM "Cliente" WHERE "ID_Cliente"=(SELECT "ID_Cliente" FROM "Venta" WHERE "ID_Venta"=1);

 

Funciones

La figura de PROCEDURE(store procedure, procedimiento almacenado) no existe como en ORACLE o SQL SERVER. En su defecto PostgreSQL tiene FUNCTION, estas funciones pueden o no devolver un valor, eso dependerá de lo que necesites hacer, bien pueden no devolver nada, simplemente ejecutan una acción, o pueden devolver hasta una tabla como resultado de algún proceso, eso el programador lo define.

La función tiene tres partes:

q  El encabezado que define el nombre de la función y el tipo de retorno.

q  El cuerpo de la función.

La especificación del lenguaje utilizado.

Sintaxis:

CREATE FUNCTION [nombre de la función] ([parámetros separados por comas]) RETURNS [el tipo de dato que regresa] AS
'DECLARE aquí se definen las variables que se usarán.
BEGIN indica el inicio de la función.

Cuerpo de la función.
RETURN Sale de la función y regresa el tipo de dato que se declaro después de la palabra RETURNS del CREATE FUNCTION
END indica el fin de la función
 LANGUAGE indica con que lenguaje esta escrita la función, puede ser un lenguaje de procedimiento (plpgsql) o de consulta (SQL).

Ejemplo:

Crearemos a tabla Venta sobre la cual crearemos una función para insertar sus campos Total y Fecha.

CREATE TABLE "Venta"

(

  "ID_Venta" serial NOT NULL PRIMARY KEY ,

  "ID_Cliente" integer NOT NULL ,

  "ID_Producto" integer NOT NULL  ,

  "Cantidad" integer NOT NULL,

  "Total" money NOT NULL,

  "Fecha" date NOT NULL,

  FOREIGN KEY ("ID_Cliente") REFERENCES  "Cliente" ("ID_Cliente"),

  FOREIGN KEY ("ID_Producto")REFERENCES "Producto" ("ID_Producto")

  );

Función que calculará los campos Total y Fecha para enseguida insertarlos en la tabla Venta:

CREATE OR REPLACE FUNCTION inserta_venta(ID_Cliente integer, ID_Producto integer, Cantidad integer)  RETURNS void AS   $$

declare Total money;

begin

 Total:=(select "Precio_Unitario" from "Producto"

where "ID_Producto"=ID_Cliente)*Cantidad;

INSERT INTO "Venta"("ID_Cliente", "ID_Producto", "Cantidad", "Total","Fecha")

VALUES ( ID_Cliente,ID_Producto ,Cantidad ,Total,NOW());

end

$$ language plpgsql;

Ejecución de la función inserta_venta:

select inserta_venta(1,1,2);

Desencadenador(trigger)

sintaxis

CREATE TRIGGER nombre { BEFORE | AFTER } { INSERT | UPDATE | DELETE [ OR ... ] } ON tabla [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE nombre de función ( argumentos )

Ejemplo de función y desencadenador:

Creamos la tabla números, con la cual se probará el desencadenador:

CREATE TABLE numeros(

numero bigint NOT NULL,

cuadrado bigint,

cubo bigint,

raiz2 real,

raiz3 real,

PRIMARY KEY (numero)

);

La siguiente function calculará los campos cuadrado, cubo, raiz2 y raiz3 a partir de una inserción o actualización en el campo numero:

CREATE OR REPLACE FUNCTION rellenar_datos() RETURNS TRIGGER AS $$

BEGIN

NEW.cuadrado := power(NEW.numero,2);

NEW.cubo := power(NEW.numero,3);

NEW.raiz2 := sqrt(NEW.numero);

NEW.raiz3 := cbrt(NEW.numero);

RETURN NEW;

END

$$ language plpgsql;

El siguiente Desencadenador ejecutará la function rellenar_datos antes de insertar o actualizar la tabla numeros:

CREATE TRIGGER rellenar_datos BEFORE INSERT OR UPDATE ON numeros FOR EACH ROW EXECUTE PROCEDURE rellenar_datos();

A continuación se realiza una inserción para probar el desencadenador:

INSERT INTO  numeros(numero) VALUES (8);