Retourner plusieurs champs comme un enregistrement dans PostgreSQL avec PL/pgSQL

Question

Je suis en train d'écrire un SP, en utilisant PL/pgSQL.
Je veux renvoyer un enregistrement, composé de champs provenant de plusieurs tables différentes. Cela pourrait ressembler à quelque chose comme ceci :

CREATE OR REPLACE FUNCTION get_object_fields(name text)
  RETURNS RECORD AS $$
BEGIN
  -- fetch fields f1, f2 and f3 from table t1
  -- fetch fields f4, f5 from table t2
  -- fetch fields f6, f7 and f8 from table t3
  -- return fields f1 ... f8 as a record
END
$$ language plpgsql; 

Comment puis-je retourner les champs de différentes tables comme champs d'un seul enregistrement ?

[Edit]

Je me suis rendu compte que l'exemple que j'ai donné ci-dessus était légèrement trop simpliste. Certains des champs que je dois récupérer seront enregistrés sous forme de lignes distinctes dans la table de base de données interrogée, mais je veux les renvoyer dans la structure d'enregistrement "aplatie".

Le code ci-dessous devrait aider à illustrer davantage :

CREATE TABLE user (id int, school_id int, name varchar(32));

CREATE TYPE my_type AS (
  user1_id   int,
  user1_name varchar(32),
  user2_id   int,
  user2_name varchar(32)
);

CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
  RETURNS my_type AS $$
DECLARE
  result my_type;
  temp_result user;
BEGIN
  -- for purpose of this question assume 2 rows returned
  SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;
  -- Will the (pseudo)code below work?:
  result.user1_id := temp_result[0].id ;
  result.user1_name := temp_result[0].name ;
  result.user2_id := temp_result[1].id ;
  result.user2_name := temp_result[1].name ;
  return result ;
END
$$ language plpgsql

Answer 1

N'utilisez pas CREATE TYPE pour retourner un résultat polymorphe. Utilisez et abusez de la Type de RECORD à la place. Regarde ça :

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Arbitrary expression to change the first parameter
  IF LENGTH(a) < LENGTH(b) THEN
      SELECT TRUE, a || b, 'a shorter than b' INTO ret;
  ELSE
      SELECT FALSE, b || a INTO ret;
  END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Faites attention au fait qu'il peut facultativement retourner deux ou trois colonnes en fonction de l'entrée.

test=> SELECT test_ret('foo','barbaz');
             test_ret             
----------------------------------
 (t,foobarbaz,"a shorter than b")
(1 row)

test=> SELECT test_ret('barbaz','foo');
             test_ret             
----------------------------------
 (f,foobarbaz)
(1 row)

Cela fait des ravages dans le code, il faut donc utiliser un nombre cohérent de colonnes, mais c'est ridiculement pratique pour renvoyer des messages d'erreur optionnels avec le premier paramètre qui renvoie le succès de l'opération. Réécrit en utilisant un nombre cohérent de colonnes :

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Note the CASTING being done for the 2nd and 3rd elements of the RECORD
  IF LENGTH(a) < LENGTH(b) THEN
      ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT);
  ELSE
      ret := (FALSE, (b || a)::TEXT, NULL::TEXT);
   END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Presque jusqu'à la chaleur épique :

test=> SELECT test_ret('foobar','bar');
   test_ret    
----------------
 (f,barfoobar,)
(1 row)

test=> SELECT test_ret('foo','barbaz');
             test_ret             
----------------------------------
 (t,foobarbaz,"a shorter than b")
(1 row)

Mais comment diviser ces données en plusieurs lignes pour que la couche ORM de votre choix puisse convertir les valeurs dans les types de données natifs du langage de votre choix ? L'actualité :

test=> SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT);
 a |     b     |        c         
---+-----------+------------------
 t | foobarbaz | a shorter than b
(1 row)

test=> SELECT a, b, c FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT);
 a |     b     | c 
---+-----------+---
 f | barfoobar | 
(1 row)

C'est l'une des fonctionnalités les plus cool et les plus sous-utilisées de PostgreSQL. Faites passer le mot.

Answer 2

Vous devez définir un nouveau type et définir votre fonction pour qu'elle renvoie ce type.

CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10) /* , ... */ );

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
RETURNS my_type 
AS 
$$

DECLARE
  result_record my_type;

BEGIN
  SELECT f1, f2, f3
  INTO result_record.f1, result_record.f2, result_record.f3
  FROM table1
  WHERE pk_col = 42;

  SELECT f3 
  INTO result_record.f3
  FROM table2
  WHERE pk_col = 24;

  RETURN result_record;

END
$$ LANGUAGE plpgsql; 

Si vous voulez renvoyer plus d'un enregistrement, vous devez définir la fonction comme suit returns setof my_type

---

Mise à jour

Une autre option consiste à utiliser RETURNS TABLE() au lieu de créer un TYPE qui a été introduit dans Postgres 8.4

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
  RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ )
...

Answer 3

Pour retourner une seule ligne

Plus simple avec OUT paramètres :

CREATE OR REPLACE FUNCTION get_object_fields(_school_id int
                                       , OUT user1_id   int
                                       , OUT user1_name varchar(32)
                                       , OUT user2_id   int
                                       , OUT user2_name varchar(32)) AS 
$func$
BEGIN
   SELECT INTO user1_id, user1_name
          u.id, u.name
   FROM   users u
   WHERE  u.school_id = _school_id
   LIMIT  1;  -- make sure query returns 1 row - better in a more deterministic way?

   user2_id := user1_id + 1; -- some calculation

   SELECT INTO user2_name
          u.name       
   FROM   users u
   WHERE  u.id = user2_id;
END
$func$  LANGUAGE plpgsql;

Appelez :

SELECT * FROM get_object_fields(1);

• Tu ne le fais pas. besoin de pour créer un type juste pour cette fonction plpgsql. Il mai est utile si vous souhaitez lier plusieurs fonctions au même type composite. Sinon, OUT les paramètres font le travail.

• Il n'y a pas RETURN déclaration. OUT sont retournés automatiquement avec ce formulaire qui renvoie une seule ligne. RETURN est facultatif.

• Depuis OUT Les paramètres sont visibles partout dans le corps de la fonction (et peuvent être utilisés comme n'importe quelle autre variable), assurez-vous de qualifier en table les colonnes de même nom pour éviter les conflits de noms ! (Mieux encore, utilisez des noms distincts pour commencer).

Plus simple encore - pour retourner également les rangs 0-n

En général, cela peut être plus simple et plus rapide si les requêtes dans le corps de la fonction peuvent être combinées. Et vous peut utiliser RETURNS TABLE() (depuis Postgres 8.4, bien avant que la question ne soit posée) pour retourner 0-n lignes.

L'exemple ci-dessus peut être écrit comme suit :

CREATE OR REPLACE FUNCTION get_object_fields2(_school_id int)
  RETURNS TABLE (user1_id   int
               , user1_name varchar(32)
               , user2_id   int
               , user2_name varchar(32)) AS 
$func$
BEGIN
   RETURN QUERY
   SELECT u1.id, u1.name, u2.id, u2.name
   FROM   users u1
   JOIN   users u2 ON u2.id = u1.id + 1
   WHERE  u1.school_id = _school_id
   LIMIT  1;  -- may be optional
END
$func$  LANGUAGE plpgsql;

Appelez :

SELECT * FROM get_object_fields2(1);

• RETURNS TABLE est effectivement la même chose que d'avoir un groupe de OUT combinés à des paramètres RETURNS SETOF record mais plus court.

• La différence majeure : cette fonction peut retourner 0, 1 ou plusieurs lignes, alors que la première version toujours renvoie 1 ligne.
  Ajouter LIMIT 1 comme démontré pour n'autoriser que 0 ou 1 rang.

• RETURN QUERY est un moyen simple de renvoyer directement les résultats d'une requête.
  Vous pouvez utiliser plusieurs instances dans une seule fonction pour ajouter plus de lignes à la sortie.

_db<>fidèle ici_ (démontrant les deux)

Type de rangée variable

Si votre fonction est censée retourner dynamiquement les résultats avec un type de ligne différent en fonction de l'apport, lire plus ici :

• Refactoriser une fonction PL/pgSQL pour retourner le résultat de diverses requêtes SELECT.

Answer 4

Si vous avez une table avec cette disposition d'enregistrement exacte, utilisez son nom comme type, sinon vous devrez déclarer le type explicitement :

CREATE OR REPLACE FUNCTION get_object_fields
        (
        name text
        )
RETURNS mytable
AS
$$
        DECLARE f1 INT;
        DECLARE f2 INT;
        …
        DECLARE f8 INT;
        DECLARE retval mytable;
        BEGIN
        -- fetch fields f1, f2 and f3 from table t1
        -- fetch fields f4, f5 from table t2
        -- fetch fields f6, f7 and f8 from table t3
                retval := (f1, f2, …, f8);
                RETURN retval;
        END
$$ language plpgsql;

Answer 5

Vous pouvez y parvenir en utilisant simplement un ensemble d'enregistrements de retour à l'aide d'une requête de retour.

CREATE OR REPLACE FUNCTION schemaName.get_two_users_from_school(schoolid bigint)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
begin

 return query
  SELECT id, name FROM schemaName.user where school_id = schoolid;

end;
$function$

Et appelez cette fonction comme : select * from schemaName.get_two_users_from_school(schoolid) as x(a bigint, b varchar);