Comment puis-je refactoriser une fonction PL/pgSQL pour renvoyer les résultats de requêtes SELECT dynamiques avec des numéros et des types de colonnes variables ?

Refactorisez les fonctions PL/pgSQL pour renvoyer la sortie de diverses requêtes SELECT

Présentation

Vous disposez d'une fonction PL/pgSQL qui génère une requête SELECT sous forme de chaîne de texte. Vous souhaitez maintenant améliorer davantage cette fonction pour exécuter la requête générée et renvoyer les résultats réels, similaire au comportement d'une requête autonome.

Type de retour variable

Le défi est que la structure et le type de données renvoyées peuvent varier en fonction de la table sous-jacente interrogée. Une solution simple consiste à définir un type de retour fixe avec un nom de colonne commun et à convertir toutes les valeurs en texte, comme dans la fonction modifiée comme ceci :

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = 
      ORDER  BY datahora'
   USING  _id;
END
$func$;

Gestion d'un nombre variable de colonnes

Si le nombre de colonnes renvoyées change, mais que les types restent les mêmes (par exemple, tous sont float8), vous pouvez utiliser un tableau de valeurs imbriquées :

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[])
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT datahora
           , string_to_array()  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = 
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$;

Renvoyer dynamiquement n'importe quel type de table

Pour gérer différents types de tables complètes avec différentes structures, vous pouvez profiter des types polymorphes :

CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;

Cette fonction nécessite que vous transmettiez une valeur NULL qui est convertie en type de table cible (par exemple, NULL::pcdmet). Il détermine ensuite automatiquement le type de ligne approprié et renvoie les colonnes individuelles lorsqu'il est utilisé avec SELECT * FROM data_of().

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!