Curseurs et pgsql

Dans le billet précédent, nous avons parlé de l'utilisation des variables dans des instructions SQL.
Nous avons regretté le fait que
SELECT ...
INTO ...
sert à créer une nouvelle table et non pas à donner une valeur à une variable.
En effet nous aurions aimé pouvoir exécuter
SELECT code_u
INTO :code
FROM utilisations
WHERE signification = 'Santé';
et ensuite
SELECT *
FROM dépenses
WHERE code_u = :'code';
Mais cela ne fonctionne pas.

D'où l'idée de créer avec le langage PL/pgSQL une procédure (appelée par une fonction), procédure dans laquelle SELECT... INTO... fonctionne comme nous le voulons.
Voici les instructions qui nous ont servi à créer cette fonction:

CREATE FUNCTION fedépenses (text)
RETURNS SETOF dépenses AS $$
DECLARE
code dépenses.code_u%TYPE;
usage alias for $1;
fedépenses refcursor;
dépenses RECORD;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
OPEN fedépenses FOR SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
 LOOP 
 FETCH NEXT FROM fedépenses INTO dépenses;
   EXIT WHEN NOT FOUND;
 RETURN NEXT dépenses;
END LOOP;
CLOSE fedépenses;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Le 'RETURNS SETOF' est nécessaire si on veut que la fonction puisse retourner un ensemble de rangées.
La variable code est définie avec le même type que le champ dépenses.code_u.
fedépenses est déclaré en tant que curseur non lié. Il est lié à un query seulement lors de son ouverture.
Pour le reste, assez classiquement nous passons en revue avec FETCH les différentes rangées lisibles par le curseur et ce jusqu'à la dernière.
Après avoir créé la fonction, il reste à la tester:

L'output est calamiteux (EDIT: voir plus loin), mais nous avons ce que nous voulons.
Nous avons également testé une variante de la fonction précédente:

CREATE FUNCTION fe2dépenses (text)
RETURNS SETOF dépenses AS $$
DECLARE
code dépenses.code_u%TYPE;
usage alias for $1;
fedépenses CURSOR (code text) IS 
 SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
FOR dépenses IN fedépenses (code) LOOP
RETURN NEXT dépenses;
 END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Le curseur est maintenant déclaré lié à un query paramétré. Le curseur est ouvert automatiquement avec l'instruction FOR (il ne doit pas être ouvert avant) et il est fermé automatiquement lorsque la boucle se termine. Chaque rangée lue par le curseur est successivement assignée au record dépenses qui est créé automatiquement et qui n'existe que pendant la durée de la boucle.
Et voici le test:

Il n'est d'ailleurs pas nécessaire de définir un curseur. Ceci fonctionne également:

CREATE FUNCTION fe3dépenses (text)
RETURNS SETOF dépenses AS $$
DECLARE
code dépenses.code_u%TYPE;
usage alias for $1;
d dépenses%ROWTYPE;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
 FOR d IN SELECT * FROM dépenses WHERE code_u = code ORDER BY référence
 LOOP 
 RETURN NEXT d;  
END LOOP;
RETURN;
END;
$$ LANGUAGE 'plpgsql';

Mais dans tous les cas, l'output est toujours aussi calamiteux.

EDIT: pour cette fonction (fe3dépenses), SELECT * FROM permet d'éviter l'output calamiteux. Il en est de même pour les deux fonctions précédentes à condition que le type indiqué dans la clause RETURNS soit 'dépenses' et non 'refcursor' qui ne fonctionne plus. Ceci est possible car la sortie de ces fonctions est constituée de rangées complètes (avec toutes les colonnes) d'une seule table.

Alors pourquoi ne pas se servir de PL/pgSQL uniquement pour créer un curseur et utiliser celui dans le terminal psql?
Pour cela, créons la fonction ocdépenses:

CREATE FUNCTION ocdépenses (refcursor, text)
RETURNS refcursor AS $$
DECLARE
usage alias for $2;
code dépenses.code_u%TYPE;
BEGIN
SELECT code_u
INTO code
FROM utilisations
WHERE signification = usage;
OPEN $1 for SELECT * FROM dépenses WHERE code_u = code ORDER BY référence;
RETURN $1;
END;
$$ LANGUAGE 'plpgsql';

Testons:

Ah oui: le curseur n'est vivant que pendant la durée d'une transaction. Or la transaction qui a été implicitement ouverte au lancement du query
select ocdépenses('curs1', 'Carburant')
est fermée automatiquement lorsque celui-ci s'est exécuté.
Corrigeons le tir en ouvrant explicitement la transaction avec BEGIN:

COMMIT termine la transaction et ferme donc le curseur.

Certains diront que l'on pouvait arriver à ce résultat avec:

select *
from dépenses
where code_u =
(select code_u
from utilisations
where signification = 'Carburant')
;
et même avec
select A.*
from dépenses A natural join utilisations B
where signification = 'Carburant'
;

A cela nous répondons:

1. c'est beaucoup moins amusant
2. le SQL se trouve alors au niveau du client ce qui dans le cas de traitement lourd peut présenter certains désavantages (ce n'est pas le cas ici)
3. le but était de parler des variables
4. comment alors présenter nos amis les curseurs?

Et les curseurs offrent de nombreuses possibilités. On n'est pas obligé de se limiter à 'fetch all'. Par exemple, si curs1 a été défini pour 'Grande surface':

etc...

Suite au 'fetch all', le curseur est positionné après la dernière rangée. C'est pourquoi l'instruction suivante nous amène à l'avant-dernière rangée. Le deuxième 'fetch relative -2' montre que l'on remonte bien de 2 positions.