Cursors i control d'errors

En aquest apartat estudiarem com fem el control dels errors produïts en temps d’execució dins d’un bloc PL/PgSQL.

Per defecte, qualsevol error que es produeix en una funció PL/PgSQL avorta l’execució de la funció i, de fet, de les transaccions següents. Si volem capturar els errors i recuperar-nos-en caldrà fer-ho mitjançant l’ús d’un bloc BEGIN amb una clàusula EXCEPTION.

La sintaxi és una extensió de la sintaxi normal d’un bloc BEGIN:

[ <<etiqueta>> ] 
[ DECLARE 
declaracions ] 
BEGIN 
sentències 
EXCEPTION 
WHEN condició [ OR condició ... ] THEN 
sentència-gestora 
[ WHEN condició [ OR condició ... ] THEN 
sentència-gestora 
... ] 
END; 

Si no hi ha error, aquesta forma de bloc simplement executa totes les sentències, i després el control passa a la instrucció següent després d’aquest END. Però si es produeix un error dins d’una sentència, el tractament de les sentències posteriors s’abandona, i el control passa a la llista d’excepcions. Dins la llista es busca la condició que coincideixi l’error que s’ha produït. Si es troba una coincidència, la sentència gestora corresponent (handler_statement) s’executa, i després el control passa a la instrucció següent després d’aquest END. Si no hi ha coincidència, l’error es propaga com si la clàusula d’excepció no hi fos en absolut: l’error pot ser atrapat per un bloc que el conté amb l’excepció, o si no n’hi ha cap que anul·li el processament de la funció.

Els noms de condició poden ser qualssevol dels que es mostren a les taules següents.

El nom d’una categoria, o classe, coincideix amb qualsevol error que pertanyi a aquesta categoria.

La condició especial OTHERS coincideix amb cada tipus d’error excepte amb QUERY_CANCELED.

Els noms de condició es poden donar en minúscules o majúscules. També una condició d’error pot ser especificada per SQLSTATE.

WHEN division_by_zero THEN ... 
WHEN SQLSTATE ’22012’ THEN ... 

Quan un error és capturat per una clàusula EXCEPTION, les variables locals de la funció PL/PgSQL romanen com estaven quan va ocórrer l’error, però tots els canvis d’estat de la base de dades persistents dins del bloc es desfan. Com a exemple, aquest fragment:

INSERT INTO mytab(firstname, lastname) VALUES(’Pere’, ’Martí’); 
BEGIN 
UPDATE mytab SET firstname = ’Josep’ WHERE lastname = ’Martí’; 
x := x + 1; 
y := x / 0; 
EXCEPTION 
WHEN division_by_zero THEN 
RAISE NOTICE ’capturat error division_by_zero’; 
RETURN x; 
END; 

Quan el control arriba a l’assignació de y, fallarà amb un error de division_by_zero. Aquest serà capturat per la clàusula EXCEPTION.

El valor retornat en el compte de retorn és el valor incrementat de x, però els efectes de l’ordre UPDATE s’han revertit.

La instrucció INSERT anterior al bloc no es reverteix, però, de manera que el resultat final és que la base de dades conté “Pere Martí” i no “Josep Martí”.

Dins d’un manejador d’excepcions, la variable SQLSTATE conté el codi d’error que correspon a l’excepció que s’ha plantejat (vegeu les taules següents per a una llista de possibles codis d’error). La variable SQLERRM conté el missatge d’error associat a l’excepció. Aquestes variables no estan definides fora de controladors d’excepció.

Aquest exemple utilitza un manegador d’excepcions per fer cada UPDATE o INSERT, de manera apropiada:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT); 
 
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS 
$$ 
BEGIN 
LOOP 
-- Primerament intentem actualitzar amb el valor de key 
UPDATE db SET b = data WHERE a = key; 
IF found THEN 
RETURN; 
END IF; 
-- Aqui haurem finalitzat, però si algú intenta inserir la clau 
-- de manera concurrent, 
-- podem tenir una errada de clau no única 
BEGIN 
INSERT INTO db(a,b) VALUES (key, data); 
RETURN; 
EXCEPTION WHEN unique_violation THEN 
-- no fer res i tornem a intentar actualitzar un altre cop 
END; 
END LOOP; 
END; 
$$ 
LANGUAGE plpgsql; 
 
 
 
SELECT merge_db(1, ’David’); 
SELECT merge_db(1, ’Marcel’); 

El PostgreSQL no disposa d’un model gaire acurat de tractament d’excepcions. Quan l’analitzador, l’optimitzador o l’executor decideixen que una sentència no es pot processar, la transacció completa s’avorta i el sistema torna per processar la consulta següent de l’aplicació.

L’única cosa que fa PL/PgSQL quan es produeix un avortament d’execució durant l’execució d’una funció o disparador és enviar missatges de depuració al nivell DEBUG, indicant en quina funció i on (número de línia i tipus de sentència) ha succeït l’error.

Es pot utilitzar la sentència RAISE per enviar missatges:

RAISE level ‘format’ [,identificador[...]]; 

en què level pot ser, per exemple, NOTICE o EXCEPTION.

• NOTICE escriu en la bitàcola de la base de dades i ho envia a l’aplicació del client.
• EXCEPTION escriu en la bitàcola de la bases de dades i avorta la transacció.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

L’opció de nivell especifica la gravetat de l’error.

Els nivells permesos són DEBUG, LOG, INFO, NOTICE, WARNING i EXCEPTION, i EXCEPTION és el nivell per defecte.

EXCEPTION genera un error (que normalment avorta la transacció actual) i els altres nivells només generen missatges de diferents nivells de prioritat.

Si els missatges són d’una prioritat especial, s’informa el client, o per escrit en el registre del servidor, o tots dos, i es controlen amb les variables de configuració log_min_messages i client_min_messages.

Després del nivell, si s’escau, es pot escriure un format (que ha de ser una simple cadena literal, no una expressió).

La cadena de format especifica el text del missatge d’error de què s’informa. La cadena de format pot ser seguida per les expressions amb argument opcional que s’insereixen en el missatge. Dins del format de cadena % se substitueix pel valor de l’argument següent.

En aquest exemple, el valor de v_job_id substituirà % dins la cadena:

RAISE NOTICE ’Cridant cs_create_job(%)’, v_job_id; 

Es pot adjuntar informació addicional a l’informe d’error emprant USING seguit per opció_clau = expressió. Les opcions_clau permeses són MESSAGE, DETAIL, HINT i ERRCODE, en què cada expressió pot ser una cadena.

• MESSAGE estableix el text del missatge d’error (aquesta opció no es pot utilitzar en la forma de RAISE que inclou una cadena de format abans de USING).
• DETAIL proporciona un missatge de detall d’error, mentre que HINT proporciona un missatge de suggeriment. ERRCODE especifica el codi d’error (SQLSTATE) de què s’informa, ja sigui pel nom de la condició, com es mostra en la taula, o directament amb un codi de 5 caràcters SQLSTATE.

Aquest exemple avortaria la transacció i donaria el missatge d’error i suggeriment següents:

RAISE EXCEPTION ’No existeix ID --> %’, user_id 
USING HINT = ’confirma identificador de l\'usuari’; 

Aquest dos exemples mostren d’informar mitjançant SQLSTATE:

RAISE ’Identificador de l\'usuari duplicat: %’, user_id USING ERRCODE = ’unique_violation’; 
RAISE ’Identificador de l\'usuari duplicat: %’, user_id USING ERRCODE = ’23505’; 

Aquí tenim una segona sintaxi de RAISE en la qual el principal argument és el nom de la condició o el codi SQLSTATE, com per exemple:

RAISE division_by_zero; 
RAISE SQLSTATE ’22012’; 

En aquesta sintaxi, USING es pot emprar per proporcionar un missatge d’error personalitzat, detall o pista. Una altra manera d’escriure l’exemple anterior seria:

RAISE unique_violation USING MESSAGE = ’Identificador de l\'usuari duplicat: ’ || user_id; 

Encara tenim una altra variant: escriure RAISE USING o RAISE level USING i posar qualsevol cosa dins de la llista USING.

L’última variant de RAISE no té cap paràmetre. Aquesta forma només es pot utilitzar dins d’una clàusula EXCEPTION dins del bloc BEGIN, que provoca que l’error actual es gestioni per ser rellançat en el bloc inclòs següent.

Si en RAISE EXCEPTION no s’especifica cap SQLSTATE o nom de condició el valor escollit serà el genèric RAISE_EXCEPTION (P0001).

Si no s’especifica cap missatge de text s’utilitza per defecte el nom de la condició o SQLSTATE com a text del missatge.

Es recomana evitar llençar els codis d’error que acaben en tres zeros, perquè es tracta de codis de categoria i només pot ser atrapada per la captura tota la categoria.

A tots els missatges emesos pel servidor PostgreSQL s’assignen cinc caràcters de codi d’error que segueixen les convencions estàndard SQL per a codis SQLSTATE. Les aplicacions que necessiten saber quina condició d’error s’ha produït en general treballen amb el codi d’error en comptes de buscar el missatge d’error textual.

És menys probable que canviïn els codis d’error per mitjà de comunicats de PostgreSQL, i no estan subjectes a canvis a causa de la localització de missatges d’error. Tingueu en compte que alguns, però no tots, els codis d’error produïts pel PostgreSQL, estan definits per l’estàndard SQL, i alguns altres codis d’error addicionals, a causa de condicions no definides per la norma, han estat inventats o presos de sistemes gestors de bases de dades.

Segons la norma, els dos primers caràcters d’un codi d’error denoten una classe d’error, mentre que els tres últims caràcters indiquen una condició específica dins d’aquesta classe. Per tant, una aplicació que no reconeix el codi d’error específic encara pot ser capaç d’inferir a quina classe d’error pertany .

Fins ara hem estat emprant cursors implícits. Aquest tipus de cursors presenten diversos problemes. El més important és que la subconsulta cal que retorni un sol tuple, ja que en cas contrari es produiria un error.

En lloc d’executar una consulta completa d’una vegada, és possible establir un cursor que encapsuli la consulta, i després llegeixi el resultat de la consulta una o unes quantes files a la vegada. Una de les raons per fer això és per evitar desbordament de memòria quan el resultat conté un gran nombre de files. (No obstant això, normalment els usuaris de PL/PgSQL no s’han de preocupar per això, ja que per als bucles FOR de manera automàtica es fa ús d’un cursor internament per evitar problemes de memòria.)

Un ús més interessant és el de retornar una referència a un cursor en què s’ha creat una funció, i permetre que el que fa la crida pugui llegir les files. Això proporciona una manera eficaç de retornar grans conjunts de files des de les funcions.

Hi ha quatre operacions bàsiques per treballar amb un cursor explícit:

• Declaració del cursor: CURSOR. Aquest tipus de variable es declara a la zona DECLARE.
• Obertura del cursor: OPEN. S’obre aquest cursor a la zona d’instruccions. Això comporta que s’executi automàticament la sentència SELECT associada i el seu resultat s’emmagatzema en estructures internes de memòria gestionades pel cursor.
• Recollida d’informació FETCH. En aquest instant es recull la informació d’una fila i s’emmagatzema en les variables corresponents.
• Tancament del cursor: CLOSE. Quan el cursor no torna a ser emprat cal tancar-lo.

Tots els accessos als cursors en PL/PgSQL passen a través de variables de cursor, que són sempre del tipus de dades especial refcursor. Una manera de crear una variable de cursor és declarar una variable com del tipus refcursor. Una altra manera és utilitzar la sintaxi de la declaració del cursor, que en general és:

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query ; 

• FOR pot ser substituït per IS per assolir compatibilitat amb Oracle.
• SCROLL; si s’especifica, el cursor és capaç de desplaçar-se cap enrere.
• NO SCROLL; si s’especifica un desplaçament cap enrere serà rebutjat.
• la llista d’arguments, si s’especifica, és una llista separada per comes de variables de diferents tipus de dades que defineixen els noms per ser reemplaçats per valors dels paràmetres de la consulta donada. Els valors actuals per substituir aquests noms s’especificaran més endavant, quan s’obri el cursor.

Alguns exemples:

DECLARE 
curs1 refcursor; 
curs2 CURSOR FOR SELECT * FROM tenk1; 
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key; 

Totes tres variables tenen tipus de dades refcursor, però la primera es pot utilitzar amb qualsevol consulta, mentre que la segona té una consulta totalment especificada ja fixada a la variable curs2, i l’última té una consulta amb paràmetres vinculats a aquesta. (key serà reemplaçat per un valor de paràmetre de nombre enter, quan s’obre el cursor.)

La variable curs1 es diu que és independent, ja que el cursor no està vinculat a cap consulta en particular. Les variables curs2 i curs3 estan vinculades.

Abans d’emprar un cursor per recuperar les files, cal que aquest s’obri. (Aquesta és l’acció equivalent a la instrucció SQL DECLARE CURSOR.) PL/PgSQL té tres formes de la sentència OPEN, dues de les quals utilitzen variables de cursor no vinculades, mentre que el tercer utilitza una variable de cursor vinculada.

La sintaxi de la declaració OPEN FOR query és:

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query ; 

La variable de cursor s’obre i utilitza la consulta especificada per a l’execució. El cursor ja no es pot obrir, ja ​​que ha estat declarada com una variable cursor no vinculada (és a dir, com una variable refcursor simple). La consulta ha de ser un SELECT, o alguna altra cosa que retorna files (com EXPLAIN). La consulta es farà de la mateixa manera que altres ordres SQL en PL/PgSQL: els noms de les variables PL/PgSQL són substituïts, i el pla de consulta s’emmagatzema per a la reutilització possible. Quan una variable PL/PgSQL se substitueix en la consulta de cursor, el valor que se substitueix és el que té en el moment de l‘OPEN, els canvis posteriors a la variable no afectaran el comportament del cursor. Les opcions de desplaçament SCROLL i NO SCROLL tenen el mateix significat que per a un cursor vinculat.

Un exemple:

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; 

La sintaxi de la declaració OPEN FOR EXECUTE és:

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string 
[ USING expression [, ... ] ]; 

La variable de cursor s’obre i genera l’execució de la consulta especificada. El cursor no es pot tornar a obrir, i ​​aquest ha d’haver estat declarat com una variable del cursor no vinculada (és a dir, com una variable refcursor simple). La consulta s’especifica com una expressió de cadena, de la mateixa manera que en la instrucció EXECUTE.

Com de costum, això li dóna flexibilitat perquè el pla de consulta pot variar d’una execució a una altra, i això també significa que la substitució de variables no es faci. Igual que amb EXECUTE, els valors dels paràmetres es poden inserir de manera dinàmica emprant USING. Les opcions de desplaçament SCROLL i NO SCROLL tenen el mateix significat que per a un cursor vinculat.

Un exemple:

OPEN curs1 FOR EXECUTE ’SELECT * FROM ’ || quote_ident(tabname) 
|| ’ WHERE col1 = $1’ USING keyvalue; 

En aquest exemple, el nom de la taula s’insereix en la consulta de manera textual, emprant la funció quote_ident(). Es recomana evitar la injecció SQL. El valor de comparació per a COL1 és inserit mitjançant USING, i per això no necessiten cometes.

Aquesta forma d’obertura s’utilitza per obrir una variable de cursor que té la consulta vinculada quan es va declarar.

OPEN bound_cursorvar [ ( argument_values ) ]; 

El cursor ja no es pot tornar a obrir. Ha d’aparèixer una llista d’expressions d’argument si i només si el cursor es va declarar que tenia arguments. Aquests valors se substitueixen en la consulta. Tingueu en compte que les opcions SCROLL o NO SCROLL no poden ser determinades, ja que el comportament de desplaçament del cursor ja s’ha determinat.

Tingueu en compte que a causa de la substitució de variables que es fa a la consulta del cursor obligat, hi ha dues maneres de passar els valors a l’indicador: ja sigui amb un argument explícit per obrir, o implícitament per referència a una variable de PL/PgSQL a la consulta. No obstant això, només les variables declarades abans que el cursor es va declarar obligat seran substituïdes. En qualsevol cas, el valor que es passa es determina en el moment de l’obertura.

Exemples:

OPEN curs2; 
OPEN curs3(42); 

Una vegada que el cursor s’ha obert, es pot manipular amb les sentències descrites a continuació:

• FETCH
• MOVE
• UPDATE/DELETE WHERE CURRENT OF
• CLOSE

Aquestes manipulacions no han de passar necessàriament en la mateixa funció en què inicialment es va obrir el cursor. Una funció pot retornar un valor refcursor i deixar que posteriorment s’operi amb aquest cursor.

Internament, un valor refcursor és simplement el nom de la cadena d’un denominat portal que conté la consulta activa per al cursor. Aquest nom pot ser distribuït, assignat a altres variables refcursor, i així successivament, sense pertorbar el portal mateix.

Tots els portals es tanquen de manera implícita al final de la transacció. Per tant, un valor refcursor es pot utilitzar per fer referència a un cursor obert només fins al final de la transacció.

FETCH recupera la fila següent del cursor i es converteix en un objectiu, que podria ser una variable de fila, una variable de registre, o una llista separada per comes de variables senzilles, com SELECT INTO.

FETCH [ direction { FROM | IN } ] cursor INTO target; 

Si no hi ha una fila a continuació, l’objectiu s’estableix en NULL(s). Igual que amb SELECT INTO, la variable especial FOUND es pot comprovar per veure si una fila s’ha obtingut o no.

La clàusula de direcció pot ser qualsevol de les variants permeses en les instruccions SQL FETCH, NEXT, PRIOR, FIRST, LAST, ABSOLUTE compte, RELATIVE compte, ALL, FORWARD [ compte | ALL ], o BACKWARD [ compte | ALL ].

Cursor cal que sigui un nom d’una variable que faci referència a un portal de cursor refcursor obert.

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE reposiciona un cursor sense retornar cap dada.

MOVE [ direction { FROM | IN } ] cursor ; 

MOVE treballa exactament com la sentència FETCH, exceptuant que encara que es reposiciona el cursor no retorna cap fila. De la mateixa manera que la sentència SELECT INTO, la variable especial FOUND ens permet comprovar per veure si hi ha una fila al costat per fer el moviment.

La clàusula de direcció pot ser qualsevol de les variants permeses en les instruccions SQL FETCH, NEXT, PRIOR, FIRST, LAST, ABSOLUTE compte, RELATIVE compte, ALL, FORWARD [ compte | ALL ], o BACKWARD [ compte | ALL ].

L’omissió de la direcció és la mateixa que especifica NEXT. Els valors de la direcció que requereixen moviment cap enrere és probable que fallin, tret que el cursor s’hagi declarat o s’obri amb l’opció SCROLL.

Exemples:

MOVE curs1; 
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

Quan un cursor està situat en una fila de la taula, la fila pot ser actualitzada o esborrada amb el cursor per identificar la fila.

UPDATE table SET ... WHERE CURRENT OF cursor ; 
DELETE FROM table WHERE CURRENT OF cursor ; 

Quan un cursor està situat en una fila de la taula, la fila pot ser actualitzada o esborrada amb el cursor per identificar la fila. Hi ha restriccions en el que pot ser la consulta del cursor (en particular, sense agrupació) i el millor és utilitzar FOR UPDATE a l’indicador.

Un exemple:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1; 

CLOSE tanca en el portal subjacent un cursor obert.

CLOSE cursor ; 

Això es pot utilitzar per alliberar els recursos abans del final de la transacció, o per alliberar la variable de cursor si cal obrir-la de nou.

És molt recomanable tancar un cursor al final d’usar-lo.

Un exemple:

 CLOSE curs1; 

Les funcions PL/PgSQL poden retornar cursors quan s’executen. Això és útil per retornar diverses files o columnes, especialment amb conjunts de resultats molt grans. Per això, la funció obre el cursor i retorna el nom del cursor (o simplement s’obre el cursor amb un nom de portal especificat o conegut per l’usuari que executa la funció). L’usuari que fa la crida pot captar files del cursor. El cursor el pot tancar l’usuari que fa la crida, o aquest es tancarà automàticament quan es tanqui la transacció.

El nom del portal utilitzat per un cursor pot ser especificat pel programador o generat de manera automàtica. Per especificar un nom de portal, només ha d’assignar una cadena a la variable refcursor abans d’obrir-lo. El valor de la cadena de la variable refcursor serà utilitzada per OPEN com a nom del portal subjacent. No obstant això, si la variable refcursor és nul·la, OPEN genera automàticament un nom que no sigui incompatible amb qualsevol portal ja existent, i s’assigna a la variable refcursor.

L’exemple següent mostra una manera com el cursor pot tenir un nom de cursor subministrat per qui fa l’execució de la funció:

CREATE TABLE test (col text); 
INSERT INTO test VALUES (’123’); 
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS 
BEGIN 
OPEN $1 FOR SELECT col FROM test; 
RETURN $1; 
END; 
’ LANGUAGE plpgsql; 
BEGIN; 
SELECT reffunc(’funccursor’); 
FETCH ALL IN funccursor; 
COMMIT; 

L’exemple següent utilitza la generació automàtica de nom de cursor:

CREATE FUNCTION reffunc2() RETURNS refcursor AS ’ 
DECLARE 
ref refcursor; 
BEGIN 
OPEN ref FOR SELECT col FROM test; 
RETURN ref; 
END; 
’ LANGUAGE plpgsql; 
-- need to be in a transaction to use cursors. 
BEGIN; 
SELECT reffunc2(); 
reffunc2 
-------------------- 
<unnamed cursor 1> 
(1 row) 
FETCH ALL IN "<unnamed cursor 1>"; 
COMMIT; 

L’exemple següent mostra una manera de retornar múltiples cursors en una sola funció:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$ 
BEGIN 
OPEN $1 FOR SELECT * FROM table_1; 
RETURN NEXT $1; 
OPEN $2 FOR SELECT * FROM table_2; 
RETURN NEXT $2; 
END; 
$$ LANGUAGE plpgsql; 
-- necessitem emprar una transacció per emprar cursors. 
 
 
 
BEGIN; 
SELECT * FROM myfunc(’a’, ’b’); 
FETCH ALL FROM a; 
FETCH ALL FROM b; 
COMMIT; 

Hi ha una variant de la instrucció FOR que permet recórrer en iteració les files retornades per un cursor.

La seva sintaxi és:

[ <<label>> ] 
FOR recordvar IN bound_cursorvar [ ( argument_values ) ] LOOP 
 
statements 
END LOOP [ label ]; 

La variable de cursor ha d’haver estat vinculada a alguna consulta quan va ser declarada, i no ha d’haver estat oberta encara.

La instrucció FOR obre automàticament el cursor, i es tanca el cursor de nou quan se surt del bucle.

Cal que aparegui una llista de valors d’argument si, i només si, el cursor es va declarar que tenia arguments. Aquests valors se substitueixen a la consulta, de la mateixa manera que durant un OPEN.

La variable recordvar es defineix automàticament com a tipus de registre i només existeix dins del bucle (qualsevol definició existent de nom de la variable es té en compte dins del bucle). Cada fila retornada pel cursor s’assigna successivament a aquesta variable de registre i el cos del bucle s’executa.