Comment puis-je modifier une fonction PL/pgSQL pour renvoyer les résultats d'une requête SQL dynamique ?

Modifier la fonction PL/pgSQL pour renvoyer les résultats de la requête

Contexte

Au départ, vous créez une fonction qui génère une chaîne de requête PostgreSQL SELECT bien formée. Vous souhaitez maintenant exécuter l'instruction SELECT générée directement sur la base de données et récupérer ses résultats.

Méthodes : SQL dynamique et types de retour

SQL dynamique

Pour exécuter du SQL dynamique, nous utilisons la commande EXECUTE. Puisque vous n'utilisez pas de curseur, vous pouvez utiliser la syntaxe RETURN QUERY EXECUTE.

Type de retour

Le défi consiste à renvoyer des enregistrements de type non défini puisque la fonction doit déclarer le type de retour. Nous envisagerons différentes approches en fonction des caractéristiques spécifiques des données.

Solution simple utilisant un type de retour fixe

En supposant des types de retour fixes pour les colonnes d'horodatage, de texte et de texte, nous pouvons définir la fonction comme suit :

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora, %s
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _id;
END
$$ LANGUAGE plpgsql;</code>

Nombre variable de colonnes du même type

Si vous avez un nombre variable de colonnes, et que toutes les colonnes sont du même type (par exemple double), nous pouvons utiliser le type ARRAY pour imbriquer les valeurs :

<code class="language-sql">CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
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
$$ LANGUAGE plpgsql;</code>

Une variété de types de tables complètes

Pour renvoyer toutes les colonnes du tableau, on peut utiliser des types polymorphes :

<code class="language-sql">CREATE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement
AS $$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT *
       FROM   %I  -- pg_typeof returns regtype, quoted automatically
       WHERE  id = 
       ORDER  BY datahora',
      pg_typeof(_tbl_type)
   )
   USING _id;
END
$$ LANGUAGE plpgsql;</code>

Remarque : dans le deuxième exemple, la fonction string_to_array nécessite un délimiteur, et , est ajouté ici comme délimiteur. Le troisième exemple utilise l'identifiant de formatage %I pour gérer les noms de table de manière plus sûre afin d'empêcher l'injection SQL. La méthode que vous choisissez dépend de vos besoins spécifiques et de la structure de vos données. Si possible, fournir plus d'informations sur la structure de votre table et les résultats souhaités peut m'aider à fournir une solution plus précise et plus efficace.

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!