Procédure multi-requete et php...

davidl · 26/05/2009 11:33:44

Bonjour,

Voici plusieurs jours que je bloque sur un truc qui me parait pourtant très simple.
Je ne trouve pas d'exemple, de tuto, ni d'aide sur cela.

Je vous explique...

Je voudrais faire une procédure stockée en postgresql qui renvoie deux jeux de résultats.

Par exemple :

--
-- Name: testdle8(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION testdle8() RETURNS SETOF record
    AS $$SELECT 123 AS truc1 ;
SELECT u_id AS truc2 FROM utilisateur ;
SELECT 456 AS truc3 ;$$
    LANGUAGE sql;
ALTER FUNCTION public.testdle8() OWNER TO admin;

NB : Ne cherchez pas de cohérence entre les deux requètes, il n'y en a pas dans cet exemple ;-)

Puis, dans mon code PHP, je voudrais pouvoir afficher les noms des utilisateurs puis les titres des actualités.

Voilà, c'est aussi simple que cela.

Lorsque je fait cela et que j'appelle ma fonction, seul le dernier jeu de résultats est accessible. Aussi bien directement sur phpPgAdmin qu'en php...

Pour finir, mon code php :

<?php 
try {
    $hostname = "localhost";
    $dbname = "db";
    $username = "login";
    $pw = "pwd";
    $pdo = new PDO ("pgsql:host=$hostname;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
  }
      $query = $pdo->prepare("select testdle8()");
      $query->execute();
     
      for($i=0; $row = $query->fetch(); $i++){
	var_dump($row) ;
        echo $i." - ".$row[0]."<br/>";
      }

      unset($pdo);
      unset($query);
?>

Merci pour votre aide...
David

Marc Cousin · 26/05/2009 18:29:27

http://docs.postgresql.fr/8.3/xfunc-sql.html

34.4.5. Fonctions SQL renvoyant un ensemble
Quand une fonction SQL est déclarée renvoyer un SETOF un_type, la requête finale SELECT de la fonction est complètement exécutée et chaque ligne extraite est renvoyée en tant qu'élément de l'ensemble résultat.

Seule la dernière requête finale est exécutée dans une procédure SQL.
Il faut partir sur une procédure PLPgSQL dans le cas présent je pense.

Marc Cousin · 26/05/2009 19:42:12

J'y pense, au passage, tu peux faire un UNION ALL des requêtes si tu veux avoir en retour l'ensemble des résultats de toutes les requêtes.

davidl · 27/05/2009 09:15:34

OK mais comment traduire cela en PLPgSql ? Tu as une idée ?

Merci de ta réponse !
David

Marc Cousin · 27/05/2009 21:44:21

Pas la peine de faire du plpgsql. Justement le plus simple c'est de faire un UNION dans le SQL :

CREATE FUNCTION testdle8() RETURNS SETOF record
AS $$SELECT 'truc1' as nom_truc, 123 UNION ALL
SELECT 'truc2', u_id FROM utilisateur UNION ALL
SELECT 'truc3', 456;$$
LANGUAGE sql;

davidl · 27/05/2009 22:41:37

Bonsoir,

J'ai bien compris qu'il y a des solutions "intermédiaires"... Je connais assez bien le SQL pour pouvoir en trouver moi-meme. Mais je ne veux pas "bidouiller"....

Je vais essayer de donner un exemple concret qui serait difficile à réaliser avec les UNION, même si encore une fois, cet exemple est "débile".

Donc imaginons une procédure qui fasse une requete sur tous les utilisateurs d'une table. Et imaginons que chaque utilisateur aie une table à lui tout seul. Imaginons encore que dans cette table, il y ait une colonne qui indique un "id" et un type d'info. Du genre id = 548 et que cet id est de type "commande" mais cela pourrait tout aussi bien être id = 284 et de type "message". Pour chacun de ses utilisateurs, je voudrais récupérer ses infos, plus ses infos dans "sa" table plus les infos liées à la table "type" grace à l'id.

C'est assez compliqué ?

Va t'en me sortir d'union maintenant... Surtout si j'ajoute encore quelques couches...

Je suis habitué à MySQL (très limité) et à SQL Serveur avec lequel je sais parfaitement faire ce que je cherche... En ASP avec SQL Serveur, vous pouvez faire une procédure qui fait deux, trois, quatre, ..., SELECT la suite. Et dans le code ASP, vous récupérer le premier jeu de résultat et vous passez au suivant avec "NextRecordSet()".

J'ai l'impression que cela n'est pas possible avec Postgresql et que c'est pour ca que personne ne comprend ce que je cherche à faire......... Je crois que si cela était faisable, on trouverait l'option "SETOF recordS" ou "SETOF record[]", ou un truc du genre....

PS : Je rappelle que ce que j'écris sort tout droit de mon imagination, il est évident que le gars qui ferait un truc du genre serait vraiment tordu... :-)

David

Marc Cousin · 28/05/2009 09:35:58

Ok, je voulais pas être vexant, il y a de tous les niveaux sur le forum.

Il n'y a pas de souci pour le faire en PLPgSQL ou dans un autre langage. C'est juste que je cherchais la solution la plus simple

Il faut faire une fonction qui retourne un SET OF

Ensuite, retourner les enregistrements un à un avec des RETURN NEXT
http://docs.postgresql.fr/8.3/plpgsql-c … tures.html

Les deux exemples ci-dessous sont tirés de bouts de codes réels que je viens de rechercher rapidement.
Désolé, il y a donc plus que ce que tu demandes, mais d'un autre côté ça illustre ce qu'on peut faire de façon un peu plus riche (même si le code n'est pas des plus élégants ) :

Un exemple en PLPgSQL :

CREATE OR REPLACE FUNCTION debit_moyen_periode(pdate_debut timestamp with time zone, pdate_fin timestamp with time zone, phost inet, pperiode bigint)
  RETURNS SETOF date_octets_sens AS
$BODY$DECLARE
	curs1 refcursor;
	curs2 refcursor;
	vinterval interval;
	vdate_prec timestamptz;
	voctets_prec bigint;
	vsens_prec char;
	vdate timestamptz;
	voctets bigint;
	vsens char;
	vretour date_octets_sens;
	

BEGIN
	CREATE temp TABLE t1 AS SELECT * 
		FROM prep_debit_moyen_periode(pdate_debut, pdate_fin, phost);
	vinterval:=INTERVAL '1 second'*pperiode;
	OPEN curs1 FOR EXECUTE 'SELECT * FROM t1';
	OPEN curs2 FOR EXECUTE 'SELECT * FROM t1';

	LOOP
		-- On recupere l'enreg qu'on veut mesurer
		FETCH curs1 INTO vdate_prec, voctets_prec, vsens_prec;
		RAISE DEBUG 'recup curs1 : %',vdate_prec;
		LOOP
			FETCH curs2 INTO vdate, voctets, vsens;
			RAISE DEBUG 'recup curs2 : %',vdate;
			IF vsens <> vsens_prec OR vdate_prec + vinterval <= vdate OR NOT FOUND THEN
				EXIT;
			END IF;
		END LOOP;
		-- Soit on a trouve, soit on a change de sens, soit on est arrive a la fin
		IF NOT FOUND THEN
			-- On est arrives au bout
			RAISE DEBUG 'Fin traitement';
			EXIT;
		ELSIF vsens <> vsens_prec THEN
			-- On a change de sens. On saute les enregistrements restant et on passe a l'autre sens
			RAISE DEBUG 'Changement de sens';
			LOOP
				FETCH curs1 INTO vdate_prec, voctets_prec, vsens_prec;
				IF vsens_prec = vsens THEN
					EXIT;
				END IF;
			END LOOP;
		ELSE
			-- On a trouve ce qu'on cherche
			RAISE DEBUG 'Trouve';
			vretour.date:=vdate;
			vretour.valeur:=ROUND((voctets-voctets_prec)::real/(EXTRACT(EPOCH FROM (vdate-vdate_prec))));
			vretour.sens:=vsens;
			RETURN NEXT vretour;
		END IF;
		

	END LOOP;
	CLOSE curs1;
	CLOSE curs2;
	DROP table t1;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Un exemple en PLPerl :

CREATE OR REPLACE FUNCTION exporte_vj(c_barre text, site text)
  RETURNS SETOF text AS
$BODY$# La requete. On recupere tout de la vue
my ($c_barre,$site)=@_;
my $where_c_barre='';
my $where_site='';
my $requete='';
if ($c_barre =~ /^NULL$/i)
{
	$where_c_barre = 'c_barre IS NULL';
}
elsif ($c_barre =~ /^NOT NULL$/i)
{
	$where_c_barre = 'c_barre IS NOT NULL';
}
else
{
	$where_c_barre = "c_barre = '$c_barre'";
}

if ($site =~ /^NULL$/i)
{
	$where_site = 'site IS NULL';
}
elsif ($site =~ /^NOT NULL$/i)
{
	$where_site = 'site IS NOT NULL';
}
else
{
	$where_site = "site = '$site'";
}
$query="SELECT * FROM vj WHERE $where_c_barre AND $where_site";
elog(DEBUG,$query);
my $sth = spi_query($query);

my $row;
return_next("computer;caract;valeur");
# On recupere tous les enreg de la requete un par un
while (defined ($row = spi_fetchrow($sth))) 
{
       my $c_barre = $row->{c_barre};
       foreach my $col (sort keys %{$row})
       {
           # pas la peine de traiter le computername
           next if ($col eq 'c_barre');
           # on retourne une ligne avec le computername, la propriete et sa valeur
           return_next($c_barre . ";" . $col. ";" . $row->{$col})
       }
}
return undef;$BODY$
  LANGUAGE 'plperl' VOLATILE;

J'espère que ça répond à la question...

davidl · 28/05/2009 12:40:48

On approche ;-)

J'ai fait cela :

CREATE OR REPLACE FUNCTION testdle9()
  RETURNS SETOF mon_retour AS
$BODY$DECLARE
    curs1 refcursor;
    curs2 refcursor;
    u_id bigint;
    t_id bigint;
    vretour mon_retour;
    

BEGIN
    OPEN curs1 FOR EXECUTE 'SELECT u_id FROM utilisateur';

    LOOP
        FETCH curs1 INTO u_id;
            vretour.u_id:=u_id;
            RETURN NEXT vretour;
        END IF;
    END LOOP;
    CLOSE curs1;

    OPEN curs2 FOR EXECUTE 'SELECT t_id FROM type';
    LOOP
        FETCH curs2 INTO t_id;
            vretour.u_id:=t_id;
            RETURN NEXT vretour;
        END IF;
    END LOOP;
    CLOSE curs2;
END$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

D'abord, "mon_retour", c'est un type... Tu as donc créer un type "date_octet_sens" ? Ou alors c'est autre chose : une vue, ... ?

Ensuite, cela reste toujours pour moi du "remplissage de valeurs" que l'on passe ensuite...

Il n'est pas possible de sortir "directement" le résultat d'une requète un peu plus simplement ?

C'est vraiment chelou...

Marc Cousin · 28/05/2009 14:46:22

oui, j'ai créé un type (avec un create type, ou je lui ai précisé les noms et types des colonnes à retourner).
On peut retourner le résultat de toute une requête avec RETURN QUERY (mais il faut que les types des colonnes de la requête correspondent au type que la fonction retourne)

davidl · 28/05/2009 15:24:38

OK, mais je ne vais pas creer un type par requete à récupérer...
Et pour le return query, si j'ai 2 requetes bien différentes ? Ca donne quoi ?

NB : Tu connais SQL Server ? Si oui, je te filerai un exemple d'une procédure stockée en TSQL.

David

gleu · 30/05/2009 00:48:54

SI tu as deux requêtes différentes qui renvoient deux types de données, tu ne peux pas utiliser la même fonction. Ce serait comme écrire une fonction en C qui pourrait renvoyer un coup une structure, un autre coup une autre structure.

Bref, le RETURN NEXT n'est pas une bidouille et c'est le seul moyen pour renvoyer plusieurs lignes de plusieurs requêtes différentes. Il existe aussi le RETURN QUERY qui renvoie le résultat (possiblement multilignes) d'une (seule) requête.

davidl · 01/06/2009 16:05:20

OK, vu avec Marc en direct, ce que je veux faire n'existe pas.
Le faire serait de la bidouille en PLSQL.

Ah... y'a des moments je regrette quand même mon SQL Server ;-)

David

NB : Vous pouvez clore le débat...

#1 26/05/2009 11:33:44

Procédure multi-requete et php...

#2 26/05/2009 18:29:27

Re : Procédure multi-requete et php...

#3 26/05/2009 19:42:12

Re : Procédure multi-requete et php...

#4 27/05/2009 09:15:34

Re : Procédure multi-requete et php...

#5 27/05/2009 21:44:21

Re : Procédure multi-requete et php...

#6 27/05/2009 22:41:37

Re : Procédure multi-requete et php...

#7 28/05/2009 09:35:58

Re : Procédure multi-requete et php...

#8 28/05/2009 12:40:48

Re : Procédure multi-requete et php...

#9 28/05/2009 14:46:22

Re : Procédure multi-requete et php...

#10 28/05/2009 15:24:38

Re : Procédure multi-requete et php...

#11 30/05/2009 00:48:54

Re : Procédure multi-requete et php...

#12 01/06/2009 16:05:20

Re : Procédure multi-requete et php...

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux