Retourner le setof record (table virtuelle) de la fonction

Question

J'ai besoin d'une fonction Postgres pour retourner une table virtuelle (comme dans Oracle) avec un contenu personnalisé. La table aurait 3 colonnes et un nombre inconnu de lignes.

Je n'ai pas trouvé la bonne syntaxe sur Internet.

Imaginez ceci :

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)
  RETURNS setof record AS
DECLARE
  open_id ALIAS FOR $1;
  returnrecords setof record;
BEGIN
  insert into returnrecords('1', '2', '3');
  insert into returnrecords('3', '4', '5');
  insert into returnrecords('3', '4', '5');
  RETURN returnrecords;
END;

Comment cela s'écrit-il correctement ?

Answer 1

(Tout ceci est testé avec postgresql 8.3.7 - avez-vous une version antérieure ? Je regarde juste votre utilisation de "ALIAS FOR $1")

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR $1;
 result RECORD;
BEGIN
 RETURN QUERY SELECT '1', '2', '3';
 RETURN QUERY SELECT '3', '4', '5';
 RETURN QUERY SELECT '3', '4', '5';
END
$$;

Si vous avez un enregistrement ou une variable de ligne à retourner (au lieu d'un résultat de requête), utilisez "RETURN NEXT" plutôt que "RETURN QUERY".

Pour invoquer la fonction, vous devez faire quelque chose comme :

select * from storeopeninghours_tostring(1) f(a text, b text, c text);

Vous devez donc définir ce que vous attendez du schéma de lignes de sortie de la fonction dans la requête. Pour éviter cela, vous pouvez spécifier des variables de sortie dans la définition de la fonction :

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
 RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
BEGIN
 RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
END
$$;

(je ne sais pas vraiment pourquoi les casts supplémentaires ::text sont nécessaires... '1' est un varchar par défaut peut-être ?)

Answer 2

Toutes les réponses existantes sont dépassées ou étaient inefficaces au départ.

En supposant que vous voulez retourner trois integer colonnes.

Fonction PL/pgSQL

Voici comment procéder avec les PL/pgSQL modernes (PostgreSQL 8.4 ou plus) :

CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used
  RETURNS TABLE (a int, b int, c int) AS
$func$
BEGIN
RETURN QUERY VALUES
  (1,2,3)
, (3,4,5)
, (3,4,5)
;
END
$func$  LANGUAGE plpgsql IMMUTABLE ROWS 3;

Dans Postgres 9.6 ou plus, vous pouvez également ajouter l'option PARALLEL SAFE .

Appelez :

SELECT * FROM f_foo();

Principaux points

• Utilisez RETURNS TABLE pour définir un type de ligne ad-hoc à retourner.
  Ou RETURNS SETOF mytbl pour utiliser un type de ligne prédéfini.

• Utilisez RETURN QUERY pour renvoyer plusieurs lignes avec une seule commande.

• Utilisez un VALUES expression pour saisir manuellement plusieurs lignes. Il s'agit d'un langage SQL standard qui existe déjà pour toujours .

• Si vous avez réellement besoin d'un paramètre, utilisez un nom de paramètre (open_id numeric) au lieu de ALIAS ce qui est découragé . Dans l'exemple, le paramètre n'a pas été utilisé et juste du bruit ...

• Il n'est pas nécessaire de mettre entre guillemets des identifiants parfaitement légaux. Les guillemets ne sont nécessaires que pour forcer des noms autrement illégaux (majuscules mixtes, caractères illégaux ou mots réservés).

• La volatilité de la fonction peut être IMMUTABLE puisque le résultat ne change jamais.

• ROWS 3 est facultatif, mais puisque nous connaître combien de lignes sont retournées, nous pourrions aussi bien le déclarer à Postgres. Cela peut aider le planificateur de requêtes à choisir le meilleur plan.

SQL simple

Pour un cas simple comme celui-ci, vous pouvez utiliser une instruction SQL simple à la place :

VALUES (1,2,3), (3,4,5), (3,4,5)

Ou, si vous voulez (ou devez) définir des noms et des types de colonnes spécifiques :

SELECT *
FROM  (
   VALUES (1::int, 2::int, 3::int)
        , (3, 4, 5)
        , (3, 4, 5)
   ) AS t(a, b, c);

Fonction SQL

Vous pouvez l'intégrer dans un simple Fonction SQL à la place :

CREATE OR REPLACE FUNCTION f_foo()
   RETURNS TABLE (a int, b int, c int) AS
$func$
   VALUES (1, 2, 3)
        , (3, 4, 5)
        , (3, 4, 5);
$func$  LANGUAGE sql IMMUTABLE ROWS 3;

Answer 3

J'utilise souvent des tableaux temporaires dans mes fonctions. Vous devez créer un type de retour dans la base de données, puis créer une variable de ce type pour le retour. Vous trouverez ci-dessous un exemple de code qui fait exactement cela.

CREATE TYPE storeopeninghours_tostring_rs AS
(colone text,
 coltwo text,
 colthree text
);

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS
$BODY$
DECLARE
  returnrec storeopeninghours_tostring_rs;
BEGIN
    BEGIN 
        CREATE TEMPORARY TABLE tmpopeninghours (
            colone text,
            coltwo text,
            colthree text
        );
    EXCEPTION WHEN OTHERS THEN
        TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.
    END;
    insert into tmpopeninghours VALUES ('1', '2', '3');
    insert into tmpopeninghours VALUES ('3', '4', '5');
    insert into tmpopeninghours VALUES ('3', '4', '5');

    FOR returnrec IN SELECT * FROM tmpopeninghours LOOP
        RETURN NEXT returnrec;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

select * from storeopeninghours_tostring()

Answer 4

Pour ceux qui ont atterri ici en cherchant l'équivalent MSSQL de la création d'une table temporaire et de l'extraction de ses enregistrements comme retour... cela n'existe pas dans PostgreSQL :( - vous devez définir le type de retour. Il y a deux façons de le faire, au moment de la création de la fonction ou au moment de la création de la requête.

Voir ici : http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

Answer 5

CREATE OR REPLACE FUNCTION foo(open_id numeric, OUT p1 varchar, OUT p2 varchar, OUT p3 varchar) RETURNS SETOF RECORD AS $$
BEGIN
  p1 := '1'; p2 := '2'; p3 := '3';
  RETURN NEXT; 
  p1 := '3'; p2 := '4'; p3 := '5';
  RETURN NEXT; 
  p1 := '3'; p2 := '4'; p3 := '5';
  RETURN NEXT; 
  RETURN;
END;
$$ LANGUAGE plpgsql;