PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

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

davidl
Membre

Procédure multi-requete et php...

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

Hors ligne

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

Marc Cousin
Membre

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

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.

Hors ligne

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

Marc Cousin
Membre

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

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.


Marc.

Hors ligne

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

davidl
Membre

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

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

Merci de ta réponse !
David

Hors ligne

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

Marc Cousin
Membre

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

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;


Marc.

Hors ligne

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

davidl
Membre

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

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

Hors ligne

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

Marc Cousin
Membre

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

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 smile

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 smile ) :

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...


Marc.

Hors ligne

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

davidl
Membre

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

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...

Hors ligne

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

Marc Cousin
Membre

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

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)


Marc.

Hors ligne

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

davidl
Membre

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

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

Hors ligne

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

gleu
Administrateur

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

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.


Guillaume.

Hors ligne

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

davidl
Membre

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

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...

Hors ligne

Pied de page des forums