Objectes i col·leccions

Els sistemes gestors de bases de dades inclouen un conjunt important de tipus de dades que s’adapten a moltes aplicacions i permeten als usuaris definir els seus propis tipus de dades. Els tipus d’usuari s’emmagatzemen en les files d’una taula i tenen un identificador únic (OID). Aquest identificador es pot utilitzar per referenciar altres tipus d’usuari i així representar relacions d’associació i d’agregació.

Un tipus defineix una estructura i un comportament comuns per a un conjunt de dades.

Un tipus objecte representa una entitat del món real i es compon dels elements següents:

1. Un nom que serveix per identificar el tipus objecte.
2. Uns atributs que modelitzen l’estructura i els valors de les dades del tipus. Cada atribut pot ser d’un tipus de dades bàsic o d’un tipus d’usuari.
3. Uns mètodes que són procediments o funcions escrits en el llenguatge PL/PgSQL (emmagatzemats en la BDOR), o escrits en un llenguatge orientat a objectes (emmagatzemats externament).
   L’estructura d’un tipus objecte consta de dues parts: especificació i cos.

L’especificació és la interfície del tipus objecte a les aplicacions. És on es declaren les estructures de dades o conjunt d’atributs i els mètodes necessaris per manipular les dades. El cos defineix els mètodes, és a dir, implementa l’especificació.

En la figura podeu veure un exemple de definició de tipus de dades en pseudocodi, i com s’utilitza aquest tipus de dades per definir altres tipus objecte:

En les especificacions es troba tota la informació que un client necessita per utilitzar els mètodes, d’aquí que es consideri una interfície operacional. És possible modificar el cos sense necessitat de modificar l’especificació i d’aquesta manera no afectar les aplicacions client.

Una de les característiques de l’orientació a objectes és que en una especificació de tipus objecte els atributs s’han de declarar abans que qualsevol dels mètodes. Per tant, si una especificació de tipus només declara atributs, el cos és innecessari perquè no hi ha mètodes a implementar. Tampoc no es poden declarar atributs en el cos del tipus objecte.

En l’especificació del tipus totes les declaracions són públiques, és a dir, visibles fora del tipus objecte. En canvi, el cos pot contenir declaracions privades, que defineixen mètodes necessaris per al funcionament intern del tipus objecte. L’àmbit de les declaracions privades és local en el cos de l’objecte.

En les especificacions del tipus objecte les declaracions són públiques, en canvi en el cos les declaracions són privades.

Per entendre aquesta estructura utilitzarem l’exemple de la figura, en què es defineix un tipus objecte i una sèrie d’operacions associades.

La implementació dels mètodes utilitza els atributs declarats dins el tipus objecte. En l’exemple anterior es calcula l’àrea del rectangle amb els valors dels atributs ample i alt, i el resultat es retorna com a paràmetre de sortida. En canvi, per calcular el desplaçament es modifiquen els atributs del tipus objecte amb els valors dels paràmetres d’entrada del mètode.

Un tipus objecte encapsula dades i operacions, per la qual cosa en l’especificació només es poden declarar atributs i mètodes, però no constants, excepcions, cursors o tipus. Com a mínim en un tipus objecte hi ha d’haver un atribut; pel que fa als mètodes, són opcionals.

Un atribut es declara mitjançant un nom i un tipus. El nom ha de ser únic dins del tipus objecte, i el tipus pot ser qualsevol que suporti l’SGBD. Els tipus de dades bàsics són els següents: lògics, numèrics, de caràcters, de dates i de taules (vegeu la figura).

El tipus objecte Persona es representa com un tuple, en què apareixen atributs de diferents tipus de dades.

Les estructures de dades poden arribar a ser molt complexes, fins al punt que el tipus d’un atribut pot ser un altre tipus objecte, anomenat tipus objecte imbricat. Això permet construir tipus d’objectes complexos a partir d’objectes simples. Alguns objectes com cues, llistes i arbres són dinàmics, creixen a mesura que s’utilitzen (vegeu la figura).

El tipus objecte Client és un tipus objecte complex, ja que conté el tipus objecte Adreçacom a atribut.En aquest cas podem veure un exemple de reutilització, ja que el tipus Adreça s’ha encapsulat com a tipus independent i aquest es pot utilitzar en la declaració de tipus.

Un mètode és un subprograma declarat en una especificació de tipus. El mètode no pot tenir el mateix nom que el tipus objecte ni el de cap dels seus atributs.

Els mètodes consten d’especificació i cos, i l’especificació consisteix en el nom del mètode, una llista opcional de paràmetres i, en el cas de les funcions, un tipus de retorn. Pel que fa al cos, el codi que conté s’executa per portar a terme una operació específica.

Per cada especificació de mètode hi ha d’haver el cos corresponent del mètode. En un tipus objecte, els mètodes poden fer referència als atributs i als altres mètodes sense qualificador.

Els mètodes es poden executar sobre els objectes del seu mateix tipus. Si client_jove és una variable PL/PgSQL que emmagatzema objectes del tipus Client, llavors client_jove.numeroClient() és un mètode que retorna el número del client emmagatzemat a client_jove.

Els mètodes del mateix tipus es poden sobrecarregar. Perquè això passi haurem d’utilitzar el mateix nom en diversos mètodes si els seus paràmetres formals són diferents en número, ordre o tipus de dades. Quan s’invoca un dels mètodes, el PL/PgSQL troba el cos adequat comparant la llista de paràmetres actuals amb cadascuna de les llistes de paràmetres formals.

L’operació de sobrecàrrega no és possible en els casos següents:

1. Si els paràmetres formals es diferencien només en el mode.
2. Si les funcions només es diferencien en el tipus de retorn.

Un cop sabem com volem emmagatzemar les dades, hem de crear l’estructura que les acollirà. Per realitzar aquest procés el PostgreSQL ens ofereix diferents tipus d’estructures: el tipus simple i el tipus compost. Aquestes estructures es poden utilitzar per definir el tipus d’un atribut dins d’una taula i per definir una taula d’objectes on els camps del tipus també són els camps de la taula.

PostgreSQL permet a l’usuari crear nous tipus de dades, el nom del tipus de les quals ha de ser diferent del nom de qualsevol tipus o domini que hi hagi en la base de dades. Preveu dos tipus de dades definides per l’usuari:

1. Un tipus de dades simple, per utilitzar en les definicions de columnes de les taules.
2. Un tipus de dades compost, per utilitzar com a tipus de retorn en les funcions definides per l’usuari.
   Ofereix les declaracions següents a l’hora de crear un tipus:

CREATE TYPE nom AS
( nom_columnatipus [, ... ] )
 
CREATE TYPE nom (
INPUT = funcio_entrada,
OUTPUT = funcio_sortida
[ , RECEIVE = funcio_rebre ]
[ , SEND = funcio_enviament ]
[ , TYPMOD_IN = modificador_tipus_funcio_entrada ]
[ , TYPMOD_OUT = modificador_tipus_funcio_sortida ]
[ , ANALYZE = funcio_analitzar ]
[ , INTERNALLENGTH = { longitud_interna | VARIABLE } ]
[ , PASSEDBYVALUE ]
[ , ALIGNMENT = alineament ]
[ , STORAGE = emmagatzematge ]
[ , LIKE = tipus_com ]
[ , CATEGORY = categoria ]
[ , PREFERRED = preferit ]
[ , DEFAULT = per_defecte ]
[ , ELEMENT = element ]
[ , DELIMITER = delimitador ]
)

Els tipus objecte es poden definir com a tipus simples o com a tipus compostos.

Per al tipus de dades simple, la definició és més complexa, ja que a les funcions se’ls ha d’especificar que tractaran amb aquest tipus. D’aquesta manera les funcions el podran utilitzar en operacions, assignacions, etc.

Definirem els tipus objecte com a tipus simples, indicant-los els atributs i definint els mètodes que implementaran les seves operacions.

Habitualment, les funcions que tractaran amb aquest tipus de dades s’escriuran en llenguatge C.

A tall d’exemple, crearem el tipus nombre complex, tal com fa la documentació de PostgreSQL. En primer lloc, hem de definir l’estructura en què emmagatzemarem el tipus:

TYPEDEF STRUCTComplex { 
DOUBLEx; 
DOUBLEy;
} Complex;

Després, les funcions que el rebran o tornaran:

PG_FUNCTION_INFO_V1(complex_in); 
Datum 
complex_in(PG_FUNCTION_ARGS) {
char*str = PG_GETARG_CSTRING(0); 
double x, y; 
Complex *result;
 
if (sscanf(str, “ ( %lf , %lf )”, &x, &y) != 2) 
ereport(ERROR,(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg(“invalid input syntax for complex: \”%s\”“, str)));
 
result = (Complex *) palloc(sizeof(Complex)); 
result->x = x; 
result->y = y; 
PG_RETURN_POINTER(result);
} 
 
PG_FUNCTION_INFO_V1(complex_out);
Datum 
complex_out(PG_FUNCTION_ARGS) {
Complex *complex = (Complex *) PG_GETARG_POINTER(0); 
char*result;
result = (char *) palloc(100); 
snprintf(result, 100, “(%g,%g)”, complex->x, complex->y); PG_RETURN_CSTRING(result);
}

Ara estem en condicions de definir les funcions, i el tipus:

CREATE FUNCTION complex_in(cstring) 
RETURNS complex 
AS 'filename’ 
LANGUAGE c IMMUTABLE STRICT;
CREATE FUNCTION complex_out(complex) 
RETURNS cstring 
AS 'filename’ 
LANGUAGE c IMMUTABLE STRICT;
CREATE TYPE complex ( 
internallength = 16, 
input = complex_in, 
output = complex_out, 
alignment = double
);

La primera forma de CREATE TYPE crea un tipus compost. El tipus compost s’especifica mitjançant una llista de noms d’atributs i tipus de dades, i representa l’estructura d’una fila o registre. És el mateix que el tipus fila d’una taula, però evita crear taules dins la base de dades quan només es vol definir un tipus.

El PostgreSQL permet que els tipus compostos s’utilitzin com a tipus simples. Per exemple:

1. Una columna d’una taula es pot declarar com a tipus de dades.
2. En els mètodes o funcions es poden utilitzar com a argument o com a tipus de retorn de la funció.

Definirem els tipus objecte com a tipus compostos, encapsulant els atributs del mateix o diferent tipus sota un nom únic i sense mètodes. A continuació podem veure un exemple de definició de tipus objecte:

CREATE TYPE complex AS (
r double precision,
i double precision
);

La sintaxi de definició d’un tipus objecte (CREATE TYPE … AS) és comparable amb la sintaxi de definició d’una taula (CREATE TABLE), excepte que només els noms i tipus de camp es poden especificar, no admetrestriccions.Tingueu en compte que la paraula clau AS és essencial; sense aquesta, el sistema produirà un error de sintaxi.

Una vegada definits els tipus, aquests es poden utilitzar per definir nous tipus i taules que emmagatzemen objectes d’aquest tipus, o per definir el tipus dels atributs d’una taula.

Agafem com a exemple el tipus objecte, inventari_element. Aquest conté tres atributs de diferents tipus, que es definiran en les columnes nom, proveidor_id i preu:

CREATE TYPE inventari_element AS (
nom text,
proveidor_id integer,
preu numeric
);

Aquest tipus objecte el podem utilitzar per definir el tipus d’un atribut dins d’una taula, és a dir, per definir la columna de la taula, amb la sentència SQL CREATE TABLE:

CREATE TABLE a_mà (
element inventari_element,
quantitat integer
);

També podem utilitzar el tipus objecte com a paràmetre d’una funció:

CREATE FUNCTION preu_extensió (inventari_element, integer) RETURNS numeric 

La sentència SQL DROP TABLE permet eliminar taules:

DROP TABLE a_mà;

Les taules creades en PostgreSQL poden incloure diverses columnes ocultes que emmagatzemen informació sobre l’identificador de transacció en què poden estar implicades, la localització física del registre dins de la taula (per localitzar-la molt ràpidament) i, els més importants, l’OID i el TABLEOID.

Aquestes últimes columnes estan definides amb un tipus de dades especial anomenat identificador d’objecte (object identifier, OID) que s’implementa com un enter positiu de 32 bits. Quan s’insereix un nou registre en una taula s’hi pot assignar un número consecutiu com a OID, i el TABLEOID de la taula que li correspon.

En la programació orientada a objectes, el concepte d’OID és de vital importància, ja que es refereix a la identitat pròpia de l’objecte, la qual cosa el diferencia dels altres objectes.

Si ho apliquem en l’exemple anterior, definim la taula de la manera següent:

CREATE TABLE a_mà (
element inventari_element,
quantitat integer
) WITH OIDS;

Per observar les columnes ocultes hi hem de fer referència específicament en l’ordre de consulta select, indicant els camps oid i tableoid com també la resta de camps de la taula a_mà. Veurem amb més deteniment les ordres de consulta i inserció en apartats posteriors, però de moment suposem que hem inserit dades en la taula anterior amb la sentència SQL insert into. L’exemple següent mostra un possible retorn d’una consulta d’aquest tipus:

oid | tableoid | nom | proveidor_id | preu | quantitat
 
--------+-----------+----------+-----------------+----------+---------------
 
17242 | 17240 | galetes | 42 | 1.99 | 1000
 
(1 rows)

Aquestes columnes s’implementen per servir d’identificadors en la realització d’enllaços des d’altres taules.

La creació d’un domini en PostgreSQL consisteix en un tipus, definit per l’usuari o inclòs en l’SGBD, més un conjunt de restriccions. A diferència del tipus objecte, en la definició de dominis es poden especificar restriccions. La sintaxi la podem veure en els exemples següents:

CREATE DOMAIN country_code char(2) NOT NULL;
CREATE DOMAIN complex_positiu complex NOT NULL CHECK
(complex_major(value,(0,0)))

1. El primer domini s’ha creat basant-se en un tipus definit pel sistema, country_code, en què l’única restricció és la seva longitud.
2. En el segon domini hauríem d’haver definit l’operador complex_major que rebés dos nombres complexos i indiqués si el primer és més gran que el segon.
   

Una taula d’objectes és una classe especial de taula que emmagatzema un objecte en cada fila i que facilita l’accés als atributs d’aquests objectes com si fossin columnes de la taula. Prenem com a exemple el tipus inventari_element. Aquest conté tres atributs de diferent tipus, que es definiran en les columnes nom, proveidor_id i preu:

CREATE TYPE inventari_element AS (
nom text,
proveidor_id integer,
preu numeric
);

Aquest tipus el podem utilitzar per definir el tipus d’una taula, és a dir, indicar de quin tipus seran els objectes que s’emmagatzemaran a la taula. Utilitzarem la sentència SQL CREATE TABLE/OF:

CREATE TABLE a_mà OF inventari_element (
PRIMARY KEY (nom),
preu WITH OPTIONS DEFAULT 10
);

En crear la taula associada a un tipus, aquesta pren l’estructura del tipus, i fa que les columnes quedin determinades pels atributs del tipus. No obstant això, l’ordre CREATE TABLE pot afegir valors i restriccions a la taula.

La taula està lligada al seu tipus, de manera que si s’elimina el tipus també s’elimina la taula associada, per exemple amb la sentència DROP TYPE … CASCADE.

Quan creem el tipus inventari_element no s’accepten atributs de tipus serial. La clau primària ja s’indica en crear la taula.

Suposem que sol·licitem a la base de dades que ens mostri totes les columnes dels registres de la taula a_mà. A continuació podem veure el retorn d’una consulta d’aquesta taula. Es pot observar com les columnes de la taula són els atributs del tipus inventari_element:

nom| proveidor_id | preu 
-----------+------------------+-----------
galetes|42| 1.99
(1 rows)

PostgreSQL ofereix com a característica particular l’herència entre taules, que permet definir una taula que hereti (atributs i mètodes) d’una altra prèviament definida, segons la definició d’herència que hem vist anteriorment.

Utilitzem la taula següent, anomenada persona, especificant que volem treballar amb OID:

CREATE TABLA persona (
nom varchar (30),
adreça varchar (30)
) WITH OIDS;

A partir d’aquesta, declarem la taula estudiant com a derivada de persona:

CREATE TABLE estudiant (
carrera varchar (50),
grup char,
grau int
) INHERITS (persona);

En la taula estudiant es defineixen les columnes carrera, grup i grau, però si sol·licitem informació de l’estructura de la taula observem que també inclou les columnes definides en la taula persona, nom i adreça.

En aquest cas, la taula persona l’anomenem pare i la taula estudiant, fill.

Cada registre de la taula estudiant contindrà cinc valors perquè té cinc columnes, tres de la taula estudiant i dues que hereta de la taula persona.

L’herència no solament permet que la taula fill contingui les columnes de la taula pare, sinó que estableix una relació conceptual entre aquestes.

Suposem que prèviament hem inserit les dades següents en la taula estudiant:

1. un estudiant de nom: ‘Joan Miquel’,
2. amb adreça: ‘Treboles 21’,
3. que estudia la carrera: ‘Eng. computacio’,
4. pertany al grup ‘A’ i grau 3.
   Si a continuació fem una consulta del contingut de la taula estudiant se’ns mostrarà un sol registre. No s’hereten les dades, únicament els camps de l’objecte –els atributs–:

nom | adreca | carrera | grup | grau
 
-----------------+--------------- +----------------------+---------+--------
 
Joan Miquel | Treboles 21 | Eng. Computació | A | 3
 
(1 rows)

I si fem una consulta de la taula persona, aquesta mostrarà un nou registre. Podem veure el retorn de la consulta:

nom | adreca 
---------------- + ----------------
Joan Miquel | Treboles 21 
(1 rows)

El registre que es mostra és el que es va inserir en la taula estudiant; tanmateix, l’herència defineix una relació conceptual en la qual un estudiant és una persona. Per tant, en consultar quantes persones estan registrades en la base de dades, tots els estudiants s’inclouen en el resultat.

No és possible esborrar una taula pare si no s’esborren primer les taules fill.

Com és lògic, en esborrar la fila del nou estudiant que hem inserit aquesta s’esborra de les dues taules, tant si l’esborrem des de la taula persona com si l’esborrem des de la taula estudiant.

Els OID (identificadors d’objectes) permeten que es diferenciïn els registres de totes les taules, encara que siguin heretades: el nostre estudiant tindrà el mateix OID en les dues taules, ja que es tracta d’una única instància de la taula estudiantque hereta de la taulapersona:

Retorn de la consulta de la taula estudiant :

oid | tableoid | nom | adreca | carrera | grup | grau
 
-------+----------+----------- +-------------+-----------------+------+------
 
**16434 **| 16431 | Joan Miquel | Treboles 21 | Eng. Computació | A | 3
 
(1 rows)

Retorn de la consulta de la taula persona:

oid | tableoid | nom | adreca 
 
--------+------------+-----------------+----------------
 
**16434 | **16427 | Joan Miquel | Treboles 21 
 
(1 rows)

Com que no es recomana l’ús d’OID en bases de dades gaire grans, i s’ha d’incloure explícitament en les consultes per examinar-ne el valor, és convenient utilitzar una seqüència compartida per a pares i tots els seus descendents si es requereix un identificador.

En el PostgreSQL, una alternativa per no utilitzar els OID és crear una columna de tipus serial en la taula pare, així serà heretada en la taula fill. El tipus serial defineix una seqüència de valors que s’anirà incrementant de manera automàtica i, per tant, constitueix una bona manera de crear claus primàries, igual que el tipus AUTO_INCREMENT en MySQL. Tornarem a crear la taula persona amb l’atribut id de tipus serial:

CREATE TABLA persona (
id serial,
nom varchar (30),
adreça varchar (30)
);

En definir un tipus serial, hem creat implícitament una seqüència independent de la taula. Una vegada la taula estigui creada, el sistema ens notificarà aquest missatge, indicant que la columna id es considera clau primària:

NOTICE: CREATE TABLE will create implicit sequence 'persona_id_seq' for SERIAL column 'persona. 
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'persona_id_key' for table 'persona'

Creem novament la taula estudiant heretant els atributs i mètodes de persona:

CREATE TABLE estudiant (
carrera varchar (50),
grup char,
grau int
) INHERITS (persona);

Estudiant heretarà la columna id i al ser de tipus serial el valor s’incrementarà utilitzant la mateixa seqüència. Suposem que inserim en la taula alguns registres d’exemple, ometent el valor per a la columna id:

• Inserim a la taula persona: (‘Josep Claramunt’ , ‘Montoliu 12’);
• Inserim a la taula persona: (‘Lluís Arnau’, ‘Barcelona 3’);
• Inserim a la taula estudiant: (‘Anna Guillen’ , ‘Tarragona 19’, ‘Psicologia’, ‘C’, 2);

Si fem una consulta sobre la taula estudiant, veurem que aquesta contindrà un sol registre, però el seu identificador serà el número 3:

id | nom | adreca | carrera | grup | grau
 
-----+----------------+-----------------+---------------+--------+------- 
 
3 | Anna Guillen | Tarragona 19 | Psicologia | C | 2 
 
(1 row)

Tots els registres de la taula persona segueixen una mateixa seqüència sense importar si són pares o fills:

id | nom | adreca 
-----+--------------------+----------------- 
1 | Josep Claramunt | Montoliu 12
2 | Lluis Arnau | Barcelona 3
3 | Anna Guillen | Tarragona 19 
(3 row)

L’herència és útil per definir taules que de manera conceptual mantenen elements en comú, però també requereixen dades que les fan diferents. Un dels elements que convé definir com a comuns són els identificadors de registre.

Si tenim el tipus següent d’objecte estudiant:

CREATE TYPE estudiant AS (
id int,
nom varchar (20),
adrecavarchar (30),
carrera varchar (50),
grup varchar(5),
grau int
);

Es pot definir una taula per emmagatzemar els estudiants del curs 2012 i una altra per emmagatzemar els estudiants que cursaran pràctiques durant l’any 2012 de la manera següent:

CREATE TABLE estudiants_2012 OF estudiant ( 
PRIMARY KEY (id)
);
CREATE TABLE estudiants_en_pràctiques ( 
empresa_pràctiques varchar(30), 
) INHERITS (estudiants_2012);

La diferència entre la primera i la segona taula és que la primera emmagatzema objectes de tipus estudiant, i la segona és una especialització de la taula anterior. És a dir, la segona taula hereta les característiques de la primera i afegeix les seves pròpies.

(1000,'Roger Llorac Arnau', 'Castalia 33', 'Psicologia', 'C', 3, 'Software Catalunya');

(1005,'Lluís Boella Domenec', 'Montoliu 55', 'Antropologia', 'B', 2);

Les regles d’integritat i de clau primària, com també la resta de propietats que es defineixin sobre una taula, només afecten els objectes d’aquesta taula, és a dir, no es refereixen a tots els objectes del tipus assignat a aquesta.

Com ja hem vist anteriorment, en els models orientats a objectes, hi ha el concepte d’identificador d’objecte (en anglès object identifier, OID), que representa la identitat de l’objecte, única per cada un.

Els OID s’encarreguen de fer referència a un objecte des d’un altre, creant d’aquesta manera les relacions entre objectes.

Hem vist que el PostgreSQL inclou en les taules creades els identificadors d’objecte, aquests es poden utilitzar per referenciar altres tipus d’usuari i així representar relacions d’associació i d’agregació entre objectes.

També som conscients que en la programació orientada a objectes, el concepte d’OID és de vital importància, ja que es refereix a la identitat pròpia de l’objecte, la qual cosa el diferencia dels altres objectes. A continuació veurem un exemple de la utilització d’OID per enllaçar dues taules.

Ens basem en la taula persona dels exemples anteriors:

CREATE TABLE persona (
nom varchar (15),
adreça varchar (30)
) WITH OIDS;

Definim un nou tipus i una nova taula per emmagatzemar els telèfons:

CREATE TYPE telefon AS (
tipusvarchar (10),
numero varchar (20),
propietarioid
);
CREATE TABLE llista_telefons OF telefon (
PRIMARY KEY(propietari)
) ;

La taula llista_telefons inclou la columna propietari de tipus OID, que emmagatzemarà la referència als registres de la taula persona. Suposem que agreguem dos telèfons a ‘Joan Miquel’; per fer la inserció utilitzem el seu OID, que és 16434. En la taula llista_telefonsinserim les dades següents:

( 'mòbil' , '12345678', 16434 ); 
( 'casa' , '987654', 16434 );

Les dues taules estan vinculades per l’OID de persona, en aquest cas el propietari, que com podem comprovar és el mateix:

-- Consulta de la taula llista_telefons
tipus | numero | propietari
---------- +--------------- +-------------- 
mòbil | 12345678 | 16434
casa | 987654 | 16434
(2 rows)

Hi ha una operació que ens permet unir les dues taules, join.En aquest cas uneix llista_telefons i persona, utilitzant la igualtat de les columnesllista_telefons.propietari i persona.oid.El resultat de la consulta anterior seria:

tipus | numero | propietari | nom | adreca
 
----------- + --------------- + ------------- + ---------------+---------------- 
 
mòbil | 12345678 | 16434| Joan Miquel | Treboles 21
 
casa | 987654 | 16434 | Joan Miquel | Treboles 21
 
(2 rows)

Els OID del PostgreSQL presenten algunes deficiències:

1. Tots els OID d’una base de dades es generen a partir d’una única seqüència centralitzada, la qual cosa provoca que, en bases de dades amb molta activitat d’inserció i eliminació de registres, el comptador de 4 bytes es desbordi i pugui lliurar OID ja lliurats. Això passa, per descomptat, amb bases de dades molt grans.
2. En les taules enllaçades mitjançant OID no s’obté cap avantatge, en termes d’eficiència, d’utilitzar operadors de composició respecte a una clau primària convencional.
3. Els OID no milloren el rendiment. En realitat, són una columna amb un nombre enter com a valor.

Els desenvolupadors de PostgreSQL proposen l’alternativa següent per utilitzar OID d’una manera absolutament segura:

1. Crear una restricció de taula perquè l’OID sigui únic, almenys en cada taula. L’SGBD anirà incrementant de manera seqüencial l’OID fins a trobar-ne un sense usar.
2. Usar la combinació OID-TABLEOID si es necessita un identificador únic per a un registre vàlid en tota la base de dades.

Els identificadors únics assignats als objectes que s’emmagatzemen en una taula permeten que aquests es puguin referenciar des dels atributs d’altres objectes.

Si volem que un atribut emmagatzemi una referència a un objecte del tipus definit, l’ordre que utilitzem és REFERENCES, que implementa una relació d’associació entre els dos tipus objecte.

En l’exemple següent apliquem l’ordre REFERENCES a un atribut:

CREATE TYPE tipus_persona AS (
dni varchar (9),
nom  varchar (30),
adreça varchar (30)
);
CREATE TABLA persona OF tipus_persona (
PRIMARY KEY (dni)
);
CREATE TYPE telefon (
tipusvarchar (10),
numero varchar (20)
);
CREATE TABLE llista_telefons (
tlftelefon,
propietari varchar(9) REFERENCES persona
) ;

Quan s’afegeixin les dades d’un nou telèfon, el PostgreSQL verificarà que el valor de propietari faci referència a una persona, i en cas contrari emetrà un missatge d’error.

Les restriccions permeten especificar condicions que hauran de complir les taules o columnes per mantenir la integritat de les dades. Algunes restriccions vénen imposades pel model concret que s’implementa, mentre que altres tenen l’origen en les regles del client o els valors que poden prendre alguns camps, entre d’altres.

Sovint el valor d’una columna és desconegut, no és aplicable o no existeix. En aquests casos, els valors zero, cadena buida o fals són inadequats, per la qual cosa utilitzarem null per especificar l’absència de valor. En definir la taula podem indicar quines columnes podran contenir valors nuls i quines no.

CREATE TABLE persona (
nom varchar(40) not null,
treball varchar(40) null,
correu varchar(20)
);

El nom d’una persona no pot ser nul, però és possible que la persona no tingui correu, ja que en no especificar una restricció not null s’assumeix que la columna pot contenir valors nuls.

Aquesta restricció s’utilitza quan no volem que els valors que conté una columna es puguin duplicar.

CREATE TABLE persona (
nom varchar(40) not null,
conjugevarchar(40) unique
);

Així, conjuge no pot contenir valors duplicats: no hem de permetre que dues persones tinguin simultàniament el mateix cònjuge.

Aquesta restricció especifica la columna o columnes que escollim com a clau primària. Hi pot haver múltiples columnes unique, però només hi ha d’haver una clau primària. Els valors que són únics poden servir per identificar una fila de la taula de manera unívoca, per la qual cosa se les anomena claus candidates.

CREATE TABLE persona (
dni varchar(10) PRIMARY KEY,
conjugevarchar(40) UNIQUE
);

En definir una columna com a primary key, es defineix implícitament com a unique. El dni no solament és únic sinó que també l’utilitzarem per identificar les persones.

En el model relacional, establim les relacions entre entitats mitjançant la inclusió de claus foranes en altres relacions. El PostgreSQL i l’SWL ofereixen mecanismes per expressar i mantenir aquesta integritat referencial. En l’exemple següent, els MeusAnimals tenen com a propietari una persona:

CREATE TABLE MeusAnimals (
nom varchar(20),
propietari varchar(10) REFERENCES persona
);

Una referència per defecte és una clau primària, per la qual cosa propietari es refereix implícitament al dnide persona. Quan es capturen les dades d’un nou animal, el PostgreSQL verifica que el valor de propietari faci referència a un dni que existeixi de persona, en cas contrari emetrà un missatge d’error. No es permet assignar un nou animal a una persona que no existeix.

També és possible especificar a quina columna de la taula es fa referència:

CREATE TABLE MeusAnimals (
nom varchar(20),
propietari varchar(10) REFERENCES persona(dni)
);

O el seu equivalent:

CREATE TABLE MeusAnimals (
nom varchar(20),
propietari varchar(10),
FOREIGN KEY propietari REFERENCES persona(dni)
);

Es podria donar el cas que la clau primària de la taula referenciada tingués més d’una columna. En aquest cas, la clau forana també hauria d’estar formada pel mateix nombre de columnes:

CREATE TABLE t1 (
a integer PRIMARY KEY,
binteger,
c integer,
FOREIGN KEY (b,c) REFERENCES other_table (c1,c2)
);

Si no s’especifica una altra acció, la persona que tingui un animal no es podrà eliminar per omissió, perquè l’animal es quedaria sense propietari. Per poder eliminar una persona, abans s’han d’eliminar els animals que pugui tenir. Aquest comportament no sembla el més adequat, així que per modificar aquest comportament disposem de les regles d’integritat referencial del llenguatge SQL, que PostgreSQL també suporta.

En l’exemple següent es permet que en eliminar una persona els animals quedin sense propietari:

CREATE TABLE MeusAnimals (
propietari varchar(10) REFERENCES persona ON DELETE SET NULL
);

Amb la clàusula on deletees poden especificar les accions següents:

1. set null. La referència pren el valor null. Si s’elimina Persona, el seu Animal es quedarà sense propietari.
2. set default. La referència pren el valor per omissió.
3. Cascade. L’acció s’efectua en cascada. Si s’elimina Persona automàticament s’eliminen els seus animals.
4. Restrict. No permet esborrar el registre. No es pot eliminar una Persona que tingui animals. Aquesta és l’acció que es pren per omissió.

Si es modifica la clau primària de la taula referenciada, es disposa de les mateixes accions que en el cas anterior, que especificarem amb la clàusula ON UPDATE.

La restriccióCheck fa l’avaluació prèvia d’una expressió lògica quan s’intenta efectuar una assignació. Si el resultat és cert, accepta el valor per a la columna; en cas contrari, emet un missatge d’error i rebutja el valor.

CREATE TABLE Persona (
edatint CHECK (edat > 10 and edat < 80),
correuvarchar(20) CHECK (correu Autoria desconeguda2012-08-28T11:18:35Al maquetador: aquesta és una expressió aleatòria d'error. Si dóna problemes en l'exportació parlem-ne, doncs probablement es puguin substituir els caràcters reservats per d'altres que no facin petar l'exportador.~ '.+@.+\..+' ),
ciutatvarchar(30) CHECK (ciutat <> “)
);

S’han restringit els valors que s’acceptaran en la columna de la manera següent:

1. edat ha de tenir un valor entre onze i setanta-nou anys.
2. ciutat no ha de ser una cadena buida.
3. correu ha de contenir una arrova.

Qualsevol d’aquestes restriccions pot tenir nom, de manera que es facilita la referència a les restriccions especifiques per esborrar-les, modificar-les, etc. Per assignar un nom a una restricció ho farem de la manera següent:

CONSTRAINT nom_de_restriccio <restriccio>

Quan les restriccions s’indiquen després de les definicions de les columnes, algunes d’aquestes poden quedar afectades simultàniament. Llavors parlem de restriccions de taula:

CREATE TABLE persona (
dni int,
nom varchar (30),
conjuge varchar(30),
cap int,
correu varchar(20),
PRIMARY KEY (dni),
UNIQUE (conjuge),
FOREIGN KEY (cap) REFERENCES persona,
CHECK (correu ~ '@' )
);

Aquesta notació permet que la restricció pugui abarcar diverses columnes.

CREATE TABLE curs (
materia varchar(30),
grup varchar (4),
dia int,
hora time,
aula int,
PRIMARY KEY (materia,grup),
UNIQUE (dia, hora, aula)
);

Un curs s’identifica pel grup i la matèria, i dos cursos no poden estar a la mateixa aula el mateix dia i a la mateixa hora.

Igual que amb la restricció de columna, a les restriccions de taula se’ls pot assignar un nom:

CREATE TABLE persona (
dniint,
nom varchar (30),
conjuge varchar(30),
cap int,
correu varchar(20),
CONSTRAINT identificador PRIMARY KEY (dni),
CONSTRAINT monogàmia UNIQUE (conjuge),
CONSTRAINT un_cap FOREIGN KEY (cap) REFERENCES persona,
CHECK (correu ~ '@' )
);

La sentència ALTER TABLE permet afegir (ADD) o treure (DROP) restriccions que ja s’han definit:

ALTER TABLE persona DROP CONSTRAINT monogàmia
ALTER TABLE ADD CONSTRAINT monogàmia UNIQUE (conjuge);

El PostgreSQL crea un índex per a les claus primàries de totes les taules. Quan necessitem crear índexs addicionals, utilitzarem l’expressió següent:

CREATE INDEX taula_camp_index ON taula (camp);

Prenent com a exemple la taula persona, quan es fa una consulta a la taula segons el valor d’un camp concret, el sistema ha d’escanejar-la sencera, fila per fila, per trobar totes les entrades coincidents. Aquest és un mètode ineficient, ja que no discrimina el fet que hi hagi poques o moltes entrades: trigarà molt a fer la consulta a l’haver de fer un registre exhaustiu. Però si al sistema se li indica que mantingui un índex en una de lescolumnes a mode d’identificador, es pot utilitzar un mètode més eficient per localitzar els registres coincidents. Per exemple, només hauria d’avançar uns nivells de profunditat en un arbre de cerca.

Es pot utilitzar l’ordre següent per crear un índex en la columna dni:

CREATE INDEX persona_dni_index ON persona (dni);

Per eliminar un índex, s’utilitza l’ordre següent:

DROP INDEX persona_dni_index;

Per poder implementar relacions 1:N, l’SGBD ha de permetre definir tipus col·lecció. Un tipus col·lecció està format per un nombre indefinit d’elements, tots del mateix tipus. D’aquesta manera, és possible emmagatzemar en un atribut un conjunt de tuples en forma d’array o en forma de taula imbricada.

El tipus de dades array és una de les característiques especials del PostgreSQL. Permet l’emmagatzematge de més d’un valor del mateix tipus en la mateixa columna.

El PostgreSQL permet que les columnes d’una taula es defineixin com a taules multidimensionals de longitud variable. Es poden crear taules de tipus simples definits per l’usuari, tipus enumerats o tipus compostos, però les taules de dominis encara no són compatibles.

Per il·lustrar l’ús de tipus array, creem la taula següent:

CREATE TYPE telefon AS (
tipusvarchar (10),
numero varchar (20)
);
CREATE TABLE llista_telefons (
nom varchar (10),
tlftelefon[]
);

La taula llista_telefons conté un atribut de tipus array que emmagatzemarà un conjunt d’objectes de tipus telefon.

La sintaxi permet especificar la mida exacta del tipus array, per exemple:

CREATE TABLE llista_telefons (
nom varchar (30),
tlftelefon[10]
);

No obstant això, definir la mida dins la sentència CREATE TABLE no afecta el comportament en temps d’execució.

Una sintaxi alternativa que s’ajusta a l’estàndard SQL utilitza la paraula clau ARRAY per a taules d’una dimensió. Si ho apliquem a l’exemple anterior es podria definir de la manera següent:

tlftelefon ARRAY 
tlf telefon ARRAY[10]

Els valors de l’array s’escriuen sempre entre claudàtors, i com passa amb qualsevol columna quan no s’especifica el contrari, s’accepten valors nuls. Vegeu un exemple de com s’inserieix un registre en la taula llista_telefonsamb un objecte de tipus telefon:

INSERT INTO llista_telefons VALUES ('Agenda feina', ARRAY[ROW('mobil','623533123')::telefon]);

Com veurem més endavant, en la sentència INSERT INTO s’utilitza l’ordre ROW, perquè el tipus de l’atribut tlf és un tipus compost.

Un cop tenim dades a llista_telefons podem executar algunes consultes sobre la taula. Per exemple, podem mostrar les dades que s’han inserit anteriorment. Ho faríem de la manera següent:

SELECT nom, tlf[1] FROM llista_telefons;
nom |tlf
-----------------+--------------------------
Agenda feina| (mobil, 623533123)

PostgreSQL ofereix un conjunt d’operadors i funcions compatibles amb el tipus array. Per exemple, si utilitzem la funció array_dims() podem conèixer les dimensions d’una taula:

SELECT array_dims(tlf) FROM llista_telefons;
[1:1]

Aquests operadors de tipus array permeten comparar i concatenar els elements de les taules. La taula mostra els tipus d’operadors que podem trobar.

També podem trobar funcions que faciliten a l’usuari la interacció amb les taules. En la taula podeu veure les diferents funcions que hi ha.