Retour de l'enregistrement (table virtuelle) à partir de la fonction

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

Je n'ai pas pu trouver la bonne syntaxe sur internet.

imaginez ceci:

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)
  RETURNS setof record AS
DECLARE
  open_id ALIAS FOR ;
  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 est-ce écrit correctement?

36
demandé sur Erwin Brandstetter 2009-06-05 14:11:34

5 réponses

(tout cela est testé avec postgresql 8.3.7-- avez-vous une version antérieure? simplement en regardant votre utilisation de "ALIAS FOR $1")

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR ;
 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);

donc vous devez définir ce que vous attendez le schéma de ligne de sortie de la fonction pour être 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
$$;

(on ne sait pas très bien pourquoi le texte supplémentaire ::est nécessaire... '1' est un varchar par défaut, peut-être?)

36
répondu araqnid 2009-06-05 10:55:09

toutes les réponses actuelles sont périmées ou étaient inefficaces pour commencer.

en supposant que vous voulez retourner trois colonnes integer .

PL/pgSQL function

Voici comment vous le faites avec moderne PL/pgSQL (PostgreSQL 8.4 ou plus tard):

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

appel:

SELECT * FROM f_foo(1);

points Importants

  • 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 retourner plusieurs lignes avec une commande.

  • utilisez l'expression VALUES pour entrer plusieurs lignes manuellement. C'est la norme SQL et a été autour de pour toujours .

  • utilisez un nom de paramètre (open_id numeric) au lieu de ALIAS , qui est déconseillé pour les noms de paramètre standard. Dans l'exemple, le paramètre n'est pas utilisé et que le bruit ...

  • pas besoin de citer deux fois des identificateurs parfaitement légaux. Les doubles citations sont seulement nécessaires pour forcer des noms autrement illégaux (caractères mixtes, 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 savoir combien de lignes sont retournées, nous pourrions aussi bien le déclarer à Postgres. Peut aider le planificateur de requête pour choisir le meilleur plan.

Simple SQL

pour un cas simple comme celui-ci, vous pouvez utiliser un simple énoncé SQL à la place:

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

Ou, si vous le voulez (ou ont) pour définir des noms de colonnes et les types:

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'envelopper dans un simple fonction SQL . Exemple sans paramètre de fonction, puisqu'il n'est pas utilisé:

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;
30
répondu Erwin Brandstetter 2017-05-23 12:10:31

j'utilise des tables temporaires un peu dans mes fonctions. Vous devez créer un type de retour sur la base de données, puis créer une variable de ce type de retour. Voici 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()
22
répondu 2009-06-05 11:46:05

à ceux qui ont atterri ici à la recherche de L'équivalent MSSQL de créer une table de température et de jeter ses dossiers que votre retour... cela n'existe pas dans PostgreSQL :( - vous devez définir le type de retour. Il y a deux façons de le faire, à l'époque de la fonction de création ou au moment de la création des requêtes.

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

8
répondu Brad Holbrook 2010-11-30 01:05:48
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;
6
répondu Milen A. Radev 2009-06-05 11:25:36