Dynamic SQL et RETURN type

^{(j'ai gardé le meilleur pour la fin, continuez à lire!)}

Vous voulez exécuter SQL dynamique . En principe, c'est simple en plpgsql avec l'aide de EXECUTE . Vous ne pas besoin d'un curseur - en fait, la plupart du temps, vous êtes mieux sans curseurs explicites.

trouver des exemples sur SO avec une recherche .

Le problème: vous voulez renvoie les enregistrements de type non défini . Une fonction doit déclarer le type de retour avec le RETURNS clause (ou avec les paramètres OUT ou INOUT ). Dans votre cas, vous devriez revenir aux dossiers anonymes, parce que nombre , noms et types des colonnes retournées varient. Comme:

CREATE FUNCTION data_of(integer)
  RETURNS SETOF record AS ...

cependant, cela n'est pas particulièrement utile. De cette façon, vous devez fournir une liste de définition de colonne avec chaque appel de la fonction. Comme:

SELECT * FROM data_of(17)
AS foo (colum_name1 integer
      , colum_name2 text
      , colum_name3 real);

mais comment feriez-vous cela, alors que vous ne connaissez pas les colonnes à l'avance?

Vous pourriez recourir à un moins structuré types de données de document comme json , jsonb , hstore ou xml :

• comment conserver une table de données (ou une liste>, ou un dictionnaire) dans une base de données?

mais pour les besoins de cette question supposons que vous voulez retourner des colonnes individuelles, correctement dactylographiées et nommées autant que possible.

solution Simple à type de retour fixe

la colonne datahora semble être une donnée, je supposerai le type de données timestamp et qu'il y a toujours deux autres colonnes avec des noms et des types de données variables.

Noms nous allons abandonner en faveur de noms génériques dans le type de retour.

Types nous allons abandonner, aussi, et tout cast à text depuis chaque type de données peut être cast à text .

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

END
$func$ LANGUAGE plpgsql;

comment ça marche?

• les variables _sensors et _type pourraient être des paramètres d'entrée à la place.

• Note la RETURNS TABLE clause.

• Remarque l'utilisation de RETURN QUERY EXECUTE . C'est l'une des plus élégante des façons de renvoie les lignes d'une requête dynamique.

• j'utilise un nom pour le paramètre de fonction, juste pour rendre la clause USING de RETURN QUERY EXECUTE moins déroutante. dans la chaîne SQL ne se réfère pas au paramètre de fonction mais à la valeur passée avec la clause USING . (Il se trouve que les deux sont dans leur champ d'application respectif dans cet exemple simple.)

• Note la valeur d'exemple pour _sensors : chaque colonne est coulée au type text .

• ce type de code est très vulnérable à injection SQL . J'utilise quote_ident() pour me protéger. Regrouper quelques noms de colonnes dans la variable _sensors empêche l'utilisation de quote_ident() (et est généralement une mauvaise idée!). S'assurer que pas mal de trucs peut-être quelques autres manière, par exemple en exécutant individuellement les noms de colonne par quote_ident() à la place. Un paramètre VARIADIC vient à l'esprit ...

plus simple avec PostgreSQL 9.1+

avec la version 9.1 ou une version ultérieure, vous pouvez utiliser format() pour simplifier davantage:

RETURN QUERY EXECUTE format('
   SELECT datahora, %s  -- identifier passed as unescaped string
   FROM   %I            -- assuming the name is provided by user
   WHERE  id = 
   ORDER  BY datahora'
  ,_sensors, _type)
USING  _id;

encore une fois, les noms de colonne individuels pourraient être échappés correctement et seraient la voie propre.

nombre Variable de colonnes partageant le même type

après la mise à jour de votre question, il semble que votre type de retour ait

• une variable nombre des colonnes
• mais toutes les colonnes de la même type double precision (alias float8 )

comme nous devons définir le type RETURN d'une fonction je recourt à un "1519400920 type" dans ce cas, qui peut contenir un nombre variable de valeurs. En outre, je renvoie un tableau avec les noms de colonne, de sorte que vous pourriez Parser les noms hors du résultat, aussi:

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[] ) AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';  -- plain list of column names
   _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$  LANGUAGE plpgsql;

divers types de tables complètes

si vous essayez réellement de retourner toutes les colonnes d'une table (par exemple l'une des tables à la page liée , puis utilisez cette solution simple et très puissante avec un polymorphic type :

CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement 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$ LANGUAGE plpgsql;

Appel:

SELECT * FROM data_of(NULL::pcdmet, 17);

remplacer pcdmet dans l'appel avec tout autre nom de table.

comment ça marche?

• anyelement est un pseudo type de données, un type polymorphique, un placeholder pour tout non-array type de données. Toutes les occurrences de anyelement dans la fonction évaluer au même type fourni à l'exécution. En fournissant une valeur d'un type défini comme argument à la fonction, nous définissons implicitement le type de retour.

• PostgreSQL définit automatiquement un type de ligne (un type de données composite) pour chaque table créée, il y a donc un type bien défini pour chaque table. Cela inclut les tables temporaires, ce qui est pratique pour une utilisation ad hoc.

• tout type peut être NULL . Nous donnons donc une valeur NULL , coulée au type table.

• maintenant la fonction retourne un type de ligne bien défini et nous pouvons utiliser SELECT * FROM data_of(...) pour décomposer la ligne et obtenir des colonnes individuelles.

• pg_typeof(_tbl_type) renvoie le nom de la table comme type d'identificateur d'objet regtype . Lorsqu'ils sont convertis automatiquement en text , les identificateurs sont automatiquement cités en double et qualifiés de schéma si nécessaire. Par conséquent, l 'injection de SQL n' est pas possible. Cela peut même concerner les noms de tableaux qualifiés de schémas où quote_ident() échouerait .