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?
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?)
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.
OuRETURNS 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 deALIAS
, 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;
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()
à 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
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;