Fonction PL/pgSQL retournant une table

Nous allons montrer sur un exemple comment utiliser la clause
RETURNS TABLE
associée à
RETURN QUERY
permet d'améliorer l'output d'une fonction PL/pgSQL renvoyant un ensemble de rangées.

Pour ce faire, créons la fonction fdépenses:

CREATE FUNCTION fdépenses(text)
RETURNS TABLE (ref char(9), date_paie date, montants numeric(13,12), détail character varying(50)) AS $$
DECLARE
  code dépenses.code_u%TYPE;
  usage alias for $1;
BEGIN
  SELECT code_u
  INTO code
  FROM utilisations
  WHERE signification = usage;
  RETURN QUERY SELECT référence, date_exec, montant, détails
  FROM dépenses WHERE code_u =  code  ORDER BY référence;        
END;
$$ LANGUAGE 'plpgsql';

Attention les noms des colonnes figurant dans la clause RETURNS TABLE doivent être différents des noms qui apparaissent dans le query. Si par exemple en ligne 2 on écrit 'montant' (sans s), aucun problème n'apparaît à la création de la fonction. Par contre à l'utilisation:

Supposons que tout est en ordre, donc que la fonction a été créée avec 'montants' écrit en ligne 2.
La fonction est maintenant utilisable:

Caramba! Ça ne fonctionne pas: malgré le RETURNS TABLE l'output est toujours aussi calamiteux.

En fait il faut encore faire  SELECT * FROM:

Et voilà!

Pour avoir la liberté de donner aux colonnes de la table affichée en output les mêmes noms que ceux des colonnes de la table d'origine (dépenses), il suffit de qualifier le nom des colonnes dans le query, comme ceci:

RETURN QUERY SELECT a.référence, a.date_exec, a.montant, a.détails
FROM dépenses a WHERE code_u =  code  ORDER BY référence;    

ou alors nous pouvons utiliser:

RUN QUERY EXECUTE chaîne de commande

Essayons avec:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails
FROM dépenses WHERE code_u = code ORDER BY référence';

Lorsqu'une commande est exécutée à l'intérieur d'une fonction PL/pgSQL, chaque variable apparaissant dans la commande  est traitée comme un paramètre auquel est assigné la valeur de la variable. C'était le cas auparavant pour la variable code. Ici ce n'est plus le cas: la commande est exécutée à l'extérieur de la fonction et code est considéré comme le nom d'une colonne.

Ceci:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails
FROM dépenses WHERE code_u = ' || code || ' ORDER BY référence'; 

conduit à une autre erreur:

On peut contourner le problème comme ceci:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails 
FROM dépenses WHERE code_u::numeric = ' || code || ' ORDER BY référence';  

ou le résoudre comme cela:

RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails 
FROM dépenses WHERE code_u = ' || quote_literal(code) || ' ORDER BY référence';  

mais le plus simple est quand même d'éviter les concaténations comme ci-après:

CREATE FUNCTION f2dépenses(text)
RETURNS TABLE (ref char(9), date_exec date, montant numeric(13,12), détails character varying(50))
AS $$
DECLARE
  code dépenses.code_u%TYPE;
  usage alias for $1;
BEGIN
  SELECT code_u
  INTO code
  FROM utilisations
  WHERE signification = usage;
  RETURN QUERY EXECUTE 'SELECT référence, date_exec, montant, détails
  FROM dépenses WHERE code_u = $1 ORDER BY référence' USING code;
END;
$$ LANGUAGE 'plpgsql';