Comment refactoriser une fonction PL/pgSQL pour gérer les types de retour SQL dynamiques ?

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

SQL dynamique et types de retour

Actuellement, votre fonction renvoie une chaîne de texte représentant l'instruction SELECT générée. Pour automatiser l'exécution et renvoyer les résultats, vous pouvez utiliser du SQL dynamique. Cependant, la difficulté réside dans le type de retour non défini. Les fonctions doivent définir un type de retour dans la clause RETURNS.

Solution de type retour fixe

En supposant un type de retour fixe, contenant une colonne d'horodatage nommée datahora et deux colonnes supplémentaires avec des noms et des types différents, vous pouvez implémenter ce qui suit :

<code class="language-sql">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$;</code>

Le nombre de colonnes est variable, mais le type est le même

Si le type de retour a un nombre variable de colonnes, mais que toutes les colonnes sont du même type (par exemple, double), utilisez le type tableau :

<code class="language-sql">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()  -- 作为名称
           , ARRAY[%s]            -- 作为值
      FROM   %s
      WHERE  id = 
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$;</code>

Type de tableau complet

Pour renvoyer toutes les colonnes d'un tableau, utilisez un type polymorphe :

<code class="language-sql">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 返回 regtype，自动引用
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

Comment utiliser :

<code class="language-sql">SELECT * FROM data_of(NULL::pcdmet, 17);</code>

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!