Forums PostgreSQL.fr

mystikilla

Membre

Procédure avec utilisation de WITH

Bonjour,

J'ai une requête utilisant WITH qui fonctionne (elle me retourne bien les résultat attendus) par contre lorsque
je me sers de cette même requête dans une procédure, l'appel de ma procédure ne me renvoie rien...

Y a t-il un lien avec le fait que WITH génère des tables 'temporaires' ?

Merci d'avance

gleu

Administrateur

Re : Procédure avec utilisation de WITH

WITH ne génère pas de tables temporaires. Voyez ça plutôt comme une facilité d'écriture de la requête. Concernant votre procédure stockée, je ne vois pas de lien possible. Fournir le code de votre procédure pourrait aider.

---

Guillaume.

mystikilla

Membre

Re : Procédure avec utilisation de WITH

Par exemple ma requete (simplifiée) :

WITH     tmp_table1 AS    (
                SELECT idauge
                FROM auges a
                WHERE idtypeauge=1
                ORDER BY idauge
            ),
    tmp_table2 AS    (
                SELECT idauge
                FROM auges
                where idauge not in (select idauge from tmp_table1)
                ORDER BY idauge
            )
SELECT idauge FROM tmp_table2

Cette requête renvoi une vingtaine d'enregistrements. Je l’insère dans une procédure :

CREATE OR REPLACE FUNCTION test(OUT idauge integer)
  RETURNS SETOF record AS
$BODY$
DECLARE
    rec record;
BEGIN
    FOR rec IN (
        WITH     tmp_table1 AS    (
                    SELECT idauge
                    FROM auges a
                    WHERE idtypeauge=1
                    ORDER BY idauge
                ),
        tmp_table2 AS    (
                    SELECT idauge
                    FROM auges
                    where idauge not in (select idauge from tmp_table1)
                    ORDER BY idauge
                )
        SELECT idauge FROM tmp_table2)
    LOOP
        idauge := rec.idauge;
        RETURN NEXT;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION test OWNER TO xxx;

Mais l'appel à cette procédure ne me renvoie pas mes enregistrement contrairement à des procédure similaire mais sans le WITH...

meles

Membre

Re : Procédure avec utilisation de WITH

WITH     tmp_table1 AS    (
                SELECT idauge
                FROM auges a
                WHERE idtypeauge=1
                ORDER BY idauge
            ),
    tmp_table2 AS    (
                SELECT idauge
                FROM auges
                where idauge not in (select idauge from tmp_table1)
                ORDER BY idauge
            )
SELECT idauge FROM tmp_table2

la requête ne pourrait elle pas être plus simple :

WITH tmp_table AS ( 
  SELECT idauge
  FROM auges 
  WHERE idtypeauge!=1
  ORDER BY idauge
)

ou être faite avec une une opération ensembliste ?

WITH tmp_table AS ( 
  SELECT idauge
  FROM auges 
 ORDER BY 1
 EXCEPT
 SELECT idauge
  FROM auges 
 WHERE idtypeauge = 1
)

Cordialement

mystikilla

Membre

Re : Procédure avec utilisation de WITH

Je suis totalement  d'accord pour la requete, je l'ai donnée à titre d'exemple.
Ce qui ne fonctionne pas, c'est son appel avec la procédure...

dverite

Membre

Re : Procédure avec utilisation de WITH

C'est dû potentiellement à l'erreur classique du paramètre de fonction qui a le même nom qu'une colonne utilisée dans la requête. (ici idauge)
Essaie en changeant le nom du paramètre.

---

@DanielVerite
http://blog-postgresql.verite.pro/

edlm

Membre

Re : Procédure avec utilisation de WITH

Cette requête renvoi une vingtaine d'enregistrements. Je l’insère dans une procédure :

CREATE OR REPLACE FUNCTION test(OUT idauge integer)
  RETURNS SETOF record AS
...

il me semble qu'il y a une anomalie entre le fait de déclarer idauge comme paramètre de sortie
puis d'indiquer que la fonction retourne un SETOF record.

Le RETURNS ne devrait-il pas être RETURNS SETOF integer ?

---

Éric

gleu

Administrateur

Re : Procédure avec utilisation de WITH

Si, tout à fait. Tenter de créer une fonction ainsi donne l'erreur suivante : ERROR:  function result type must be integer because of OUT parameters

record serait utilisable s'il y avait au moins deux éléments à renvoyer.

Pour revenir à la question initiale, il est possible d'imbriquer un WITH dans une procédure stockée :

b1=# CREATE OR REPLACE FUNCTION public.f1(OUT id integer)
 RETURNS SETOF integer
 LANGUAGE plpgsql
AS $function$
DECLARE
  rec record;
BEGIN
FOR rec IN
WITH a(toto) AS (VALUES(1), (2))
SELECT * FROM a
LOOP
  id := rec.toto;
  return next;
END LOOP;
END
$function$;
CREATE FUNCTION
b1=# SELECT * FROM f1();
 id 
----
  1
  2
(2 rows)

Donc il y a un soucis avec la façon dont la fonction est crée ou exécutée. Difficile de répondre plus précisément sans avoir le code réel de la fonction, les messages d'erreur affichés par PostgreSQL (et la version de PostgreSQL serait un plus évident).

---

Guillaume.

mystikilla

Membre

Re : Procédure avec utilisation de WITH

C'est dû potentiellement à l'erreur classique du paramètre de fonction qui a le même nom qu'une colonne utilisée dans la requête. (ici idauge)
Essaie en changeant le nom du paramètre.

J'ai fais le test, ca marche !!
Merci beaucoup, je m'attendais a tout sauf à ça !!! ...
J'ai beaucoup d'autres procédures ou c'est la cas et elles fonctionnent trés bien! Les mystères de l'informatique parfois!