PgSQL - Renvoyer un type composite [Résolu]

[ymoreau]

Bonjour,
Je travaille sur une procédure devant renvoyer plusieurs éléments contenant 2 valeurs. Je pensais donc naïvement renvoyer des paires de variables (récupérées dans la procédure) en spécifiant un type de retour que j'ai créé avec CREATE TYPE pour la sortie de la fonction.

Mais je n'ai pas trouvé si c'était faisable, et en cherchant j'ai lu une solution alternative qui serait de créer une table temporaire et de la remplir. Est-ce que mon idée de départ est réalisable, et si oui, est-elle meilleure ou moins moins bonne que l'autre ?
Sachant qu'il s'agit de récupérer et construire des résultats, donc potentiellement avoir plusieurs appels concurrents à cette procédure.

Merci d'avance.

[estofilo]

En plpgsql, on peut faire RETURNS type avec un type composite ou bien RETURNS SETOF type s'il s'agit de retourner plusieurs lignes de ce type composite.
C'est sans problème.

[ymoreau]

En fait ma question était plutôt comment on renvoie effectivement les valeurs, comment on construit une ligne du type composé. D'après la doc il faut renvoyer une variable de type RECORD ou ROW, mais la ligne que je veux renvoyer est composée de plusieurs variables et je ne vois pas comment les regrouper.

[estofilo]

Tu peux retourner une variable du type composé en question après avoir rempli les champs un par un.
Par exemple:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);
 
CREATE OR REPLACE FUNCTION t() returns complex AS 
$$
declare
 c complex;
begin
  c.r:=2.0;
  c.i:=-2.5;
  RETURN c;
end
$$ LANGUAGE plpgsql;
 
test=> SELECT * FROM t();
 r |  i   
---+------
 2 | -2.5

[ymoreau]

Merci de cette réponse claire !

[ymoreau]

En fait je suis confronté à un autre problème pour renvoyer mon type créé, en sortie j'obtiens une seule colonne contenant mes deux champs rassemblés en une chaîne (champ1,champ2), où la colonne est le nom de ma fonction. Aussi bien en sortie dans psql qu'au travers d'une lib (ruby).

J'ai essayé de renvoyer des lignes d'une vraie table et pas d'un type créé, c'est la même chose. C'est très embêtant pour travailler sur le résultat, et je m'étonne de ce comportement par défaut. Comment faut il s'y prendre pour récupérer une sortie semblable à une requête SQL classique ?

Merci d'avance.

Au cas où voici la fonction :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DROP TYPE IF EXISTS nearest_tags CASCADE;
CREATE TYPE nearest_tags AS (id_doc INT, id_tag BIGINT);
 
CREATE OR REPLACE FUNCTION get_nearest_tags(word VARCHAR) RETURNS SETOF nearest_tags AS $$
DECLARE
    nearest_tag_values nearest_tags;
    idtoken integer;
    token_row inverted_index%rowtype;
    position integer;
BEGIN
    SELECT t.id_token INTO idtoken FROM tokens AS t WHERE word = t.token;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Word "%" not found', word;
    END IF;
 
    -- For each row in the inverted index, i.e. for each document containing the word
    FOR token_row IN SELECT * FROM inverted_index AS ii WHERE ii.id_token = idtoken LOOP
        nearest_tag_values.id_doc := token_row.id_doc;
 
        -- For each position of the word
        FOR position IN SELECT unnest(token_row.positions) LOOP
            SELECT t.id_tag INTO nearest_tag_values.id_tag
              FROM (SELECT id_tag, parent_tag FROM tags WHERE id_doc = token_row.id_doc AND
                    starting_offset < position AND ending_offset > position) AS t
              WHERE t.id_tag NOT IN 
                  (SELECT parent_tag FROM tags WHERE id_doc = token_row.id_doc AND
                   parent_tag IS NOT NULL AND starting_offset < position AND ending_offset > position)
              ;
            RETURN NEXT nearest_tag_values;
        END LOOP;
 
    END LOOP;
    RETURN;
END
$$ LANGUAGE 'plpgsql';

[ced]

Depuis PostgreSQL 8.4 (de mémoire), les fonctions peuvent retourner un type TABLE. Plus besoin de passer par les types...
Du coup, en sortie, on récupère une sortie semblable à une requête classique.

Le lien vers la doc : http://docs.postgresql.fr/8.4/sql-createfunction.html
Il y a un exemple en bas de page, pour une fonction en SQL, mais c'est pareil en PL/PGSQL. Il suffit d'exécuter un SELECT en fin de fonction...

Voici un autre exemple :

Code :

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION toto(a int)
RETURNS TABLE(b int, c int) AS $$
BEGIN
  RETURN QUERY SELECT i, i+1 FROM generate_series(1, a) AS g(i);
END;
$$ LANGUAGE plpgsql;
 
SELECT * FROM toto(10);

[ymoreau]

Merci de ta réponse, en me renseignant sur RETURNS TABLE j'ai lu que cela retournait aussi des résultats concaténés, et que la forme du résultat dépend de la manière d'exécuter la fonction. Jusque là je faisais SELECT ma_fonction();, mais pour obtenir les résultats avec des colonnes distinctes et nommées il faut faire SELECT * FROM ma_fonction();.

Du coup je n'ai pas besoin d'utiliser RETURNS TABLE. Mais ça me semble plus concis, car ça évite de créer un type. Par contre dans les exemples, il n'y a qu'un RETURN sur une requête, dans mon cas je construis mes résultats au fur et à mesure, il n'est pas possible d'en obtenir l'ensemble en une seule ligne. Est-ce qu'il est possible de renvoyer comme avec SETOF les valeurs ligne par ligne ? Je n'ai pas trouvé d'indication dans la doc ou sur le net.

[ced]

Habituellement, quand j'ai besoin de faire ce genre de choses, je crée une table temporaire que je remplis au fur et à mesure de l'exécution de la fonction, puis je fais un SELECT final qui renvoie tout ce dont j'ai besoin.

Je pense que si tu fais plusieurs SELECT successifs, tu n'auras que les résultats de dernier SELECT (mais je n'ai jamais testé).

[estofilo]

Envoyé par YoniBlond

Par contre dans les exemples, il n'y a qu'un RETURN sur une requête, dans mon cas je construis mes résultats au fur et à mesure, il n'est pas possible d'en obtenir l'ensemble en une seule ligne. Est-ce qu'il est possible de renvoyer comme avec SETOF les valeurs ligne par ligne ?

Oui c'est faisable en procédural, il faut faire RETURN NEXT sans argument sachant que les noms de colonnes de la table sont accessibles en noms de variables (équivalents implicites de variables OUT).

Par exemple:

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION f() returns TABLE(i double precision, r double precision) AS 
$$
begin
  r:=2.0;
  i:=-2.5;
  RETURN next;
  r:=3.0;
  i:=-4.5;
  RETURN next;
end
$$ LANGUAGE plpgsql;

Résultat:

test=> select * from f();
  i   | r 
------+---
 -2.5 | 2
 -4.5 | 3

[ymoreau]

Merci, ça marche parfaitement