5. Manipulación de datos
5. Manipulación de datos Dataprix 26 Octubre, 2009 - 16:055.1. Consultas
5.1. Consultas Dataprix 26 Octubre, 2009 - 16:14Las consultas a la base de datos se realizan con el comando select, que se implementa en PostgreSQL cumpliendo en gran parte con el estándar SQL:
| Notación |
| Omitiremos las referencias comunes a SQL y sólo se mostrarán algunas de las posibilidades de consulta con PostgreSQL. Por lo que res- pecta a las funciones auxiliares, se han visto algunas en el apartado de tipos de datos y, en todo caso, se recomienda la consulta de la documentación del producto para las operaciones más avanzadas. |
demo=# select parte, tipo
demo-# from productos
demo-# where psugerido > 30
demo-# order by parte
demo-# limit 5
demo-# offset 3;
parte | tipo
------------+----------
Monitor | 1024x876
Monitor | 1024x876
Procesador | 2.4 GHz
Procesador | 1.7
Procesador | 3 GHz
(5 rows)
Al igual que MySQL, PostgreSQL admite la sentencia explain delante de select para examinar qué está ocurriendo durante una consulta:
Al igual que en el módulo de MySQL, vemos que no aprovecha los índices (básicamente porque no tenemos ninguno definido).
demo=# explain select productos.clave, parte||´`||tipo||´`||especificación as producto,
proveedores.empresa , precio from productos natural join precios natural join proveedores;
QUERY PLAN
--------------------------------------------------------------------------------
Hash Join (cost=45.00..120.11 rows=1000 width=104)
Hash Cond: (("outer".empresa)::text = ("inner".empresa)::text)
-> Hash Join (cost=22.50..72.61 rows=1000 width=104)
Hash Cond: ("outer".clave = "inner".clave)
-> Seq Scan on precios (cost=0.00..20.00 rows=1000 width=32)
-> Hash (cost=20.00..20.00 rows=1000 width=76)
-> Seq Scan on productos (cost=0.00..20.00 rows=1000 width=76)
-> Hash (cost=20.00..20.00 rows=1000 width=24)
-> Seq Scan on proveedores (cost=0.00..20.00 rows=1000 width=24)
(9 rows)
demo=#
Veamos como mejorar el rendimiento de este select:
demo=# create index empresa_idx on precios (empresa);
CREATE INDEX
demo=# create index clave_idx on precios (clave);
CREATE INDEX
demo=# explain select productos.clave, parte||´ `||tipo||´ `||especificación as producto,
proveedores.empresa , precio from productos natural join precios natural join proveedores;
QUERY PLAN
--------------------------------------------------------------------------------
Hash Join (cost=29.00..56.90 rows=20 width=104)
Hash Cond: ("outer".clave = "inner".clave)
->Seq Scan on productos (cost=0.00..20.00 rows=1000 width=76)
-> Hash (cost=28.95..28.95 rows=20 width=32)
-> Hash Join (cost=1.25..28.95 rows=20 width=32)
Hash Cond: (("outer".empresa)::text = ("inner".empresa)::text)
-> Seq Scan on proveedores (cost=0.00..20.00 rows=1000 width=24)
-> Hash (cost=1.20..1.20 rows=20 width=32)
-> Seq Scan on precios (cost=0.00..1.20 rows=20 width=32)
(9 rows)
demo=#
5.2. Actualizaciones e inserciones
5.2. Actualizaciones e inserciones Dataprix 26 Octubre, 2009 - 22:00PostgreSQL cumple con el estándar SQL en todos los sentidos para las sentencias de actualización, inserción y borrado. No define modificadores ni otros mecanismos para estas sentencias.
En determinadas cláusulas, y para tablas heredadas, es posible limitar el borrado o la actualización a la tabla padre (sin que se propague a los registros de las tablas hijas) con la cláusula only:
demo=# update only persona set nombre=`Sr. `||nombre;
UPDATE 2
demo=# select * from persona;
nombre | direccion
---------------------------+-------------
Sr. Alejandro Magno | Babilonia
Sr. Federico García Lorca | Granada 65
Juan | Treboles 21
(3 rows)
demo=#5.3. Transacciones
5.3. Transacciones Dataprix 26 Octubre, 2009 - 22:27| Ejemplo |
| Una compra puede ser una transacción que conste de dos operaciones: • Insertar un registro del pago del producto • Insertar el producto en el inventario. No se debe insertar un producto que no se haya pagado, ni pagar un producto que no esté en el inventario, por lo tanto, las dos operaciones forman una transacción. |
Definimos transacción como un conjunto de operaciones que tienen significado solamente al actuar juntas.
PostgreSQL ofrece soporte a transacciones, garantizando que ambas operaciones se realicen o que no se realice ninguna. Para iniciar una transacción, se utiliza el comando begin y para finalizarla, commit.
demo=# begin;
BEGIN
demo=# insert into productos values (`RAM´,`512MB´,`333 MHz´,60);
INSERT 17459 1
demo=# select * from productos;
parte | tipo | especificación | psugerido | clave
-----------+----------+----------------+-----------+-------
Procesador | 2 Ghz | 32 bits | | 1
Procesador | 2.4Ghz | 32 bits | 35 | 2
Procesador | 1.7 Ghz | 64 bits | 205 | 3
Procesador | 3 GHz | 64 bits | 560 | 4
RAM | 128MB | 333 MHz | 10 | 5
RAM | 256MB | 400 Mhz | 35 | 6
Dico Duro | 80 GB | 7200 rpm | 60 | 7
Disco Duro | 120 GB | 7200 rpm | 78 | 8
Disco Duro | 200 GB | 7200 rpm | 110 | 9
Disco Duro | 40 GB | 4200 rpm | | 10
Monitor | 1024x876 | 75 Hz | 80 | 11
Monitor | 1024x876 | 60 Hzs | 67 | 12
RAM | 512 MB | 333 MHz | 60 | 13
(13 rows)
demo=# insert into precios values
(`Patito´,13,67);
INSERT 17460 1El nuevo registro tiene como clave el 13 y, de momento, hasta que finalice la transacción, sólo puede verlo el usuario que lo ha insertado.
Insertamos un registro con el precio del proveedor Patito para el producto con clave 13.
demo=# commit;Al cerrar la transacción, los registros insertados ya son visibles para todos los usuarios. Si por alguna razón, por ejemplo una caída del sistema, no se ejecuta el commit, la transacción se cancela. La forma explícita de cancelar una transacción es con el comando rollback.