PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 10.18 » Langage SQL » Conversion de types » Constructions UNION, CASE et constructions relatives

10.5. Constructions UNION, CASE et constructions relatives

Les constructions SQL avec des UNION doivent potentiellement faire correspondre des types différents pour avoir un ensemble unique dans le résultat. L'algorithme de résolution est appliqué séparément à chaque colonne de sortie d'une requête d'union. Les constructions INTERSECT et EXCEPT résolvent des types différents de la même manière qu'UNION. Quelques autres constructions, incluant CASE, ARRAY, VALUES, et les fonctions GREATEST et LEAST utilisent le même algorithme pour faire correspondre les expressions qui les composent et sélectionner un type de résultat.

Résolution des types pour UNION, CASE et les constructions relatives

  1. Si toutes les entrées sont du même type et qu'il ne s'agit pas du type unknown, résoudre comme étant de ce type.

    Ceci crée un risque de disponibilité lors de l'appel, via un nom qualifié[9], de toute fonction trouvée dans un schéma permettant à des utilisateurs sans confiance de créer des objets. Un utilisateur mal intentionné peut créer une fonction de même nom qu'une fonction existante, répliquant les paramètres de la fonction et ajouter des nouveaux paramètres avec des valeurs par défaut. Ceci empêche les nouveaux appels à la fonction originale. Pour supprimer ce risque, placez les fonctions dans des schémas n'autorisant la création d'objets qu'à des utilisateurs de confiance.

  2. Si un type en entrée est un domaine, le traiter comme le type de base du domaine pour toutes les étapes suivantes. [11]

  3. Si toutes les entrées sont du type unknown, résoudre comme étant du type text (le type préféré de la catégorie chaîne). Dans le cas contraire, les entrées unknown seront ignorées pour les règles restantes.

  4. Si toutes les entrées non-inconnues ne sont pas toutes de la même catégorie, échouer.

  5. Sélectionnez le premier type en entrée qui n'est pas inconnu comme type candidat, puis considérez chaque autre type en entrée qui n'est pas inconnu, de gauche à droite. [12] Si le type candidat peut être converti implicitement vers l'autre type, mais pas vice versa, sélectionnez l'autre type comme nouveau type candidat. Puis continuez avec les entrées restantes. Si, à tout point dans ce traitement, un type préféré est sélectionné, arrêtez de considérer les entrées supplémentaires.

  6. Convertir toutes les entrées vers le type candidat final. Échoue s'il n'y a pas de conversion implicite à partir de l'entrée donnée vers le type candidat.

Quelques exemples suivent.

Exemple 10.9. Résolution de types avec des types sous-spécifiés dans une union

SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)

Ici, la chaîne de type inconnu 'b' sera convertie vers le type text.


Exemple 10.10. Résolution de types dans une union simple

SELECT 1.2 AS "numeric" UNION SELECT 1;

 numeric
---------
       1
     1.2
(2 rows)

Le littéral 1.2 est du type numeric et la valeur 1, de type integer, peut être convertie implicitement vers un type numeric, donc ce type est utilisé.


Exemple 10.11. Résolution de types dans une union transposée

SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);

 real
------
    1
  2.2
(2 rows)

Dans cet exemple, le type real (réel) ne peut pas être implicitement converti en integer (entier) mais un integer peut être implicitement converti en real ; le résultat de l'union est résolu comme étant un real.


Exemple 10.12. Résolution de fonctions à nombre d'arguments variant

CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
  LANGUAGE sql AS 'SELECT 1';
CREATE FUNCTION
 

Cette fonction accepte, mais ne requiert pas, le mot clé VARIADIC. Elle tolère des arguments entiers et numériques ;:

SELECT public.variadic_example(0),
       public.variadic_example(0.0),
       public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example
------------------+------------------+------------------
                1 |                1 |                1
(1 row)
 

Néanmoins, le premier et le deuxième appel préféreront des fonctions plus spécifiques si elles sont disponibles :

CREATE FUNCTION public.variadic_example(numeric) RETURNS int
  LANGUAGE sql AS 'SELECT 2';
CREATE FUNCTION

CREATE FUNCTION public.variadic_example(int) RETURNS int
  LANGUAGE sql AS 'SELECT 3';
CREATE FUNCTION

SELECT public.variadic_example(0),
       public.variadic_example(0.0),
       public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example
------------------+------------------+------------------
                3 |                2 |                1
(1 row)
 

Suivant la configuration par défaut et ke faut que seule la première fonction existe, le premier et le deuxième appel ne sont pas sécurisés. Tout utilisateur peut les intercepter en créant la deuxième et la troisième fonction. En faisant une correspondance exacte du type d'argument et en utilisant le mot clé VARIADIC, le troisième appel est sûr.


Exemple 10.13. Résolution de type dans une union imbriquée

SELECT NULL UNION SELECT NULL UNION SELECT 1;

ERROR:  UNION types text and integer cannot be matched
 

Cet échec survient parce que PostgreSQL traite plusieurs UNION comme une imbrication d'opérations sous forme de paires ; c'est-à-dire que cette entrée est identique à

(SELECT NULL UNION SELECT NULL) UNION SELECT 1;
 

Le UNION interne est résolu en émettant le type text, suivant les règles données ci-dessus. Puis le UNION externe a en entrée les types text et integer, amenant l'erreur observé. Le problème peut être corrigé en s'assurant que le UNION le plus à gauche dispose au moins d'une entrée du type résultant désiré.

Les opérations INTERSECT et EXCEPT procèdent de la même façon. Néanmoins, les autres constructions décrites dans cette section considèrent toutes leurs entrées en une seule étape de résolution.




[11] Un peu comme le traitement des arguments de type domaine pour les opérateurs et les fonctions, ce comportement permet un type domaine d'être préservé par un UNION ou toute construction similaire, tant que l'utilisateur veille à ce que toutes les entrées soient explicitement ou implicitement du type exact. Dans le cas contraire, le type de base du domaine sera préféré.

[12] Pour des raisons historiques, CASE traite sa clause ELSE (si elle est utilisée) comme la « première » entrée, avec les clauses THEN considérées après. Dans tous les autres cas, « de gauche à droite » signifie l'ordre dans lequel les expressions apparaissent dans le texte de la requête.