3. Acceso a un servidor PostgreSQL

3. Acceso a un servidor PostgreSQL Dataprix 20 October, 2009 - 10:32

3.1. La conexion con el servidor

3.1. La conexion con el servidor Dataprix 20 October, 2009 - 10:37
* Distintos tipos de credenciales     permiten distintos niveles de            acceso.                                                 

Antes de intentar conectarse con el servidor, debemos asegurarnos de que está funcionando y que admite conexiones, locales (el SGBD se está ejecutando en la misma máquina que intenta la conexión) o remotas.
 

En el apartado que concierne a la administración de PostgreSQL se comenta detalladamente los aspectos relacionados con el istema de usuarios, contraseñas y privilegios del SGBD.

Una vez comprobado el correcto funcionamiento del servidor, debemos disponer de las credenciales necesarias para la conexión. Para simplificar, supondremos que disponemos de las credenciales* del administrador de la base de datos (normalmente, usuario PostgreSQL y su contraseña).

3.2. El cliente psql

3.2. El cliente psql Dataprix 20 October, 2009 - 10:52

Para conectarse con un servidor, se requiere, obviamente, un programa cliente. Con la distribución de PostgreSQL se incluye un cliente, psql, fácil de utilizar, que permite la introducción interactiva de comandos en modo texto.

El siguiente paso es conocer el nombre de una base de datos residente en el servidor. El siguiente comando permite conocer las bases de datos residentes en el servidor:

~$ psql -l
List of databases
Name       | Owner    | Encoding
-----------+----------+-----------
demo       | postgres | SQL_ASCII
template0  | postgres | SQL_ASCII
template1  | postgres | SQL_ASCII
(3 rows)
~$

Para realizar una conexión, se requieren los siguientes datos:

•    Servidor. Si no se especifica, se utiliza localhost.

•    Usuario. Si no se especifica, se utiliza el nombre de usuario Unix que ejecuta psql.

•    Base de datos.

Ejemplos del uso de psql para conectarse con un servidor de bases de datos

~$ psql -d demo
~$ psql demo

Las dos formas anteriores ejecutan psql con la base de datos demo.

~$ psql -d demo -U yo
~$ psql demo yo
~$ psql -h servidor.org -U usuario -d basedatos

A partir del fragmento anterior, el cliente psql mostrará algo similar a lo siguiente:

Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
demo=#

El símbolo ‘#’, que significa que psql está listo para leer la entrada del usuario.

Notación                                               
Hemos utilizado los dos comandos de ayuda que ofrece el lenguaje:
•   \h Explica la sintaxis de entencias SQL.
•   \? Muestra los comandos nternos disponibles.
Para salir de ayuda, se presiona la tecla ‘q’.

Las sentencias SQL se envían directamente al servidor para su interpretación, los comandos internos tienen la forma \comando y ofrecen opciones que no están incluidas en SQL y son interpretadas internamente por psql.

Para terminar la sesión con psql, utilizamos el comando \q o podemos presionar Ctrl-D.

3.3. Introduccion de sentencias

3.3. Introduccion de sentencias Dataprix 20 October, 2009 - 11:14

Las sentencias SQL que escribamos en el cliente deberán terminar con ‘;’ o bien con ‘\g’:

demo=# select user;
   current_user
--------------
   postgres
(1 row)
demo=#

Cuando un comando ocupa más de una línea, el indicador cambia de forma y va señalando el elemento que aún no se ha completado.

 

demo=# select
demo-# user\g
   current_user
--------------
   postgres
(1 row)
demo=#

 

 

Indicadores de PostgreSQL
Indicador Significado
=# Espera una nueva sentencia
-# La sentencia aún no se ha terminado con “;” o \g
“# Una cadena en comillas dobles no se ha cerrado
‘# Una cadena en comillas simples no se ha cerrado
(# Un paréntesis no se ha cerrado

 

El cliente psql almacena la sentencia hasta que se le da la orden de enviarla al SGBD. Para visualizar el contenido del buffer donde ha almacenado la sentencia, disponemos de la orden ‘\p’:

demo=> SELECT
demo-> 2 * 10 + 1
demo-> \p
   SELECT
   2 * 10 + 1
demo-> \g
    ?column?
    ----------
       21
    (1 row)
demo=>

El cliente también dispone de una orden que permite borrar completamente
el buffer para empezar de nuevo con la sentencia:

demo=# select `Hola´\r
Query buffer reset
(cleared). demo=#

3.3.1. Expresiones y variables

3.3.1. Expresiones y variables Dataprix 20 October, 2009 - 11:27

El cliente psql dispone de multitud de prestaciones avanzadas; entre ellas (como ya hemos comentado), el soporte para sustitución de variables similar al de los shells de Unix:

demo=>\set var1 demostracion

Esta sentencia crea la variable ‘var1’ y le asigna el valor ‘demostración’. Para recuperar el valor de la variable, simplemente deberemos incluirla precedida de ‘:’ en cualquier sentencia o bien ver su valor mediante la orden ‘echo’:

demo=# \echo :var1
demostracion
demo-#

De la misma forma, psql define algunas variables especiales que pueden ser útiles para conocer detalles del servidor al que estamos conectados:

demo=# \echo :DBNAME :ENCODING :HOST :PORT :USER;
demo LATIN9 localhost 5432
postgres demo=#

El uso de variables puede ayudar en la ejecución de sentencias SQL:

demo=> \set var2 `mi_tabla´
demo=> SELECT * FROM :var2;
Se debe ser muy cuidadoso con el uso de las comillas y también es importante tener en cuenta que dentro de cadenas de caracteres no se sustituyen variables.

3.4. Proceso por lotes y formatos de salida

3.4. Proceso por lotes y formatos de salida Dataprix 20 October, 2009 - 11:54

Además de ser interactivo, psql puede procesar comandos por lotes almacenados en un archivo del sistema operativo mediante la siguiente sintaxis:

$ psql demo -f demo.psql

 

Aunque el siguiente comando también funciona en el mismo sentido, no es recomendable usarlo porque de este modo, psql no muestra información de depuración importante, como los números de línea donde se localizan los errores, en caso de haberlos:

 

$ psql demo < demo.psql

El propio intérprete psql nos proporciona mecanismos para almacenar en fichero el resultado de las sentencias:

* Hemos almacenado el resultado     en el fichero ‘/tmp/a.txt’.                     

•    Especificando el fichero destino* directamente al finalizar una sentencia:

demo=# select user \g /tmp/a.txt

 

•    Mediante una pipe enviamos la salida a un comando Unix:

Notación                                               
A la orden ‘\o’ se le debe espe- cificar un fichero o bien un co- mando que irá recibiendo los resultados mediante una pipe.
Cuando se desee volver a la salida estándar STDOUT, simplemente se dará la orden ‘\o’ sin ningún parámetro.

demo=# select user \g | cat > /tmp/b.txt

•    Mediante la orden ‘\o’ se puede indicar dónde debe ir la salida de las sentencias SQL que se ejecuten en adelante:

demo=# \o /tmp/
sentencias.txt demo=# select
user;
demo=# select 1+1+4;
demo=# \o
demo=# select 1+1+4;
   ?column?
  ----------
     6
  (1 row)
demo=#

•    Se puede solicitar la ejecución de un solo comando y terminar inmediatamente mediante la siguiente forma:

La salida del fichero                           
Al haber especificado que se quiere la salida en html, la podríamos redirigir a un fichero (ya hemos visto cómo hacerlo) y generar un archivo html que permitiese ver el resultado de la consulta mediante un navegador web convencional

$ psql -d demo -c "comando"

•    Se puede especificar el formato de salida de los resultados de una sentencia.
Por defecto, psql los muestra en forma tabular mediante texto. Para cambiarlo, se debe modificar el valor de la variable interna ‘format’ mediante la orden

'\pset'. Veamos, en primer lugar, la especificación del formato de salida:
demo=# \pset format html
Output format is html.
demo=# select user;
<table><tr><th>current_user</th><th>
   </th></tr><tr></tr><tr><td>postgres</td><td>
    </td></tr><tr><table><p>(1 row)
  </p>
demo=#

<p> </p>
<p>Hay otros formatos de salida, como ‘aligned’, ‘unaligned’, ‘html’ y ‘latex’. Por defecto, psql muestra el resultado en formato ‘aligned’.
 </p>
<table><tbody><tr><td>La salida de este fichero                      </td>
        </tr><tr><td>Con esta configuración, y diri- giendo la salida a un fichero, generaríamos un fichero CSV listo para ser leído en una hoja de cálculo u otro programa de importación de datos.</td>
        </tr></tbody></table><p>
Tenemos también multitud de variables para ajustar los separadores entre columnas, el número de registros por página, el separador entre registros, título de la página html, etc. Veamos un ejemplo:

<code>
demo=# \pset format
unaligned Output format is
unaligned. demo=# \pset
fieldsep `,´ Field separator
is ``,´´.
demo=# select user, 1+2+3 as resultado;
current_user,resultad
o postgres,6
(1 row)
demo=#

Para poder realizar los ejemplos del resto del presente apartado, se debe procesar el contenido del fichero demo.sql tal como se transcribe a continuación.

Contenido del fichero demo.psql

--drop table productos;
--drop table proveedores;
--drop table precios;
--drop table ganancia;
create table productos (
parte                 varchar(20),
tipo                  varchar(20),
especificación        varchar(20),
psugerido             float(6),
clave                 serial,

primary key(clave)
);

insert into productos (parte,tipo,especificación,psugerido) values
  (`Procesador´,´2 GHz´,´32 bits´,null);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Procesador´,´2.4 GHz´,´32 bits´,35);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Procesador´,´1.7 GHz´,´64 bits´,205);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Procesador´,´3 GHz´,´64 bits´,560);
insert into productos (parte,tipo,especificación,psugerido) values
  (`RAM´,´128MB´,´333 MHz´,10);
insert into productos (parte,tipo,especificación,psugerido) values
  (`RAM´,´256MB´,´400 MHz´,35);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Disco Duro´,´80 GB´,´7200 rpm´,60);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Disco Duro´,´120 GB´,´7200 rpm´,78);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Disco Duro´,´200 GB´,´7200 rpm´,110);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Disco Duro´,´40 GB´,´4200 rpm´,null);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Monitor´,´1024x876´,´75 Hz´,80);
insert into productos (parte,tipo,especificación,psugerido) values
  (`Monitor´,`1024x876´,´60 Hz´,67);
create table proveedores (
   empresa       varchar(20) not null,
   credito       bool,
   efectivo      bool,
   primary key   empresa)
);

insert into proveedores (empresa,efectivo) values (`Tecno-k´, true
); insert into proveedores (empresa,credito) values (`Patito´,
true ); insert into proveedores (empresa,credito,efectivo) values
(`Nacional´, true, true );

create table ganancia(
   venta     varchar(16),
   factor    decimal (4,2)
)

insert into ganancia values(`Al por mayor´,1.05);
insert into ganancia values(`Al por menor´,1.12);

create table precios (
  empresa               varchar(20) not null,
  clave                 int not null,
  precio float(6),

  foreign key (empresa) references proveedores,
  foreign key (clave)   references productos
);

  insert into precios values (`Nacional´,001,30.82);
  insert into precios values (`Nacional´,002,32.73);
  insert into precios values (`Nacional´,003,202.25);
  insert into precios values (`Nacional´,005,9.76);
  insert into precios values (`Nacional´,006,31.52);
  insert into precios values (`Nacional´,007,58.41);
  insert into precios values (`Nacional´,010,64.38);
  insert into precios values (`Patito´,001,30.40);
  insert into precios values (`Patito´,002,33.63);
  insert into precios values (`Patito´,003,195.59);
  insert into precios values (`Patito´,005,9.78);
  insert into precios values (`Patito´,006,32.44);
  insert into precios values (`Patito´,007,59.99);
  insert into precios values (`Patito´,010,62.02);
  insert into precios values (`Tecno-k´,003,198.34);
  insert into precios values (`Tecno-k´,005,9.27);
  insert into precios values (`Tecno-k´,006,34.85);
  insert into precios values (`Tecno-k´,007,59.95);
  insert into precios values (`Tecno-k´,010,61.22);
  insert into precios values (`Tecno-k´,012,62.29);

3.5. Usar bases de datos

3.5. Usar bases de datos Dataprix 20 October, 2009 - 16:07

La siguiente orden informa sobre las bases de datos actualmente en el SGBD.

demo=# \l
       List of databases
Name       |Owner       | Encoding
-----------+------------+-------------
demo       | postgres   | LATIN9 
template0  | postgres   | LATIN9
template1  | postgres   | LATIN9
(3 rows)
demo=#

La orden ‘\c’ permite conectarse a una base de datos:

demo=# \c demo
You are now connected to database ``demo´´.
demo=#

La consulta de la tabla que contiene la base de datos demo se realiza mediante la orden ‘\d’:

demo=# \d
List of relations
Schema | Name               | Type    | Owner
-------+--------------------+---------+----------
public |ganancia            |table    | postgres
public |precios             |table    | postgres
public |productos           |table    | postgres
public |productos_clave_seq |sequence | postgres
public |proveedores         |table    | postgres
(5 rows)

 

La orden \d es útil para mostrar información sobre el SGBD: tablas, índices, objetos, variables, permisos, etc. Podéis obtener todas las variantes de esta sentencia introduciendo \? en el intérprete de comandos.

Consulta de las columnas de cada una de las tablas:

demo-# \d proveedores
Table "public.proveedores"
Column    |Type                   | Modifiers
----------+-----------------------+-----------
empresa   | character varying(20) | not null
credito   | boolean               |
efectivo  | boolean               |
Indexes:
    "proveedores_pkey" primary key, btree (empresa)

Para crear una nueva base de datos, usaremos la sentencia create database:

mysql&gt; create database prueba;

Para eliminar una base de datos, usaremos la sentencia dropdatabase:

mysq&gt; dropdatabase prueba;