Forums PostgreSQL.fr

Geo-x

Membre

UPDATE dans la sélection d'une sélection avec curseur

Bonjour à tous,

je sollicite à nouveau vos connaissances pour le problème suivant :

J'ai des colonnes A/B/C/D dans une table.

Dans cette table les données ressemblent à celles-là :

a/b/c/-
a/b/c/-
a/b/c/-
d/f/g/-
d/f/g/-
d/f/g/-

Ce que je souhaiterais faire c'est :

1-Je sélectionne distinctement mes différents cas possibles :

a/b/c/-
d/f/g/-

2-Pour chacun de ces cas, je sélectionne toutes les lignes dans la table source, exemple cas1 :

a/b/c/-
a/b/c/-
a/b/c/-

3-Et pour ces lignes sélectionnées, je met dans une colonne D un compteur :

a/b/c/1
a/b/c/2
a/b/c/3

Qui recommence à 0 dans chaque cas:

d/f/g/1
d/f/g/2
d/f/g/3

Pour ce faire, j'ai créé une fonction sous postgres :

CREATE OR REPLACE FUNCTION test()
  RETURNS integer AS
$BODY$
DECLARE
    curseur CURSOR FOR SELECT DISTINCT A,B,C FROM table_a_modif;
    table record;
    table2 record;
    compteur integer;
BEGIN 
    compteur=1;

    FOR table IN curseur
    LOOP
	FOR table2 IN SELECT table_a_modif.A,table_a_modif.B,table_a_modif.C FROM table_a_modif,table WHERE table_a_modif.A=table.A,table_a_modif.B=table.B,table_a_modif.C=table.C
	LOOP
		UPDATE table_a_modif SET D=compteur;
		compteur=compteur+1;
        END LOOP
    FETCH NEXT FROM curseur
    END LOOP;
        
    return 1;
END

Mais vu que je ne suis pas familier du curseur (d'ailleurs je ne sais pas s'il est vraiment indispensable de l'utiliser dans ce cas précis) je ne sais pas vraiment comment m'y prendre.

Si vous auriez quelques pistes à me donner sur la direction à prendre, je vous en serais reconnaissant.

rjuju

Administrateur

Re : UPDATE dans la sélection d'une sélection avec curseur

Le curseur est une solution. Attention, votre initialisation compteur=1; devrait se trouver après le premier loop, et il manque un where dans la requête UPDATE.

La table a-t-elle une clé primaire ? S'il n'y a pas de clé étrangère, vous pouvez dupliquer la table avec le bon champ D, ensuite supprimer l'ancienne table et renommer la nouvelle, avec une requête du genre

CREATE TABLE table_temp AS SELECT a,b,c,row_number() OVER (PARTITION BY a,b,c) d
FROM table_a_modif

---

Julien.
https://rjuju.github.io/

Geo-x

Membre

Re : UPDATE dans la sélection d'une sélection avec curseur

Bonjour Rjuju,

Merci pour votre réponse, j'ai corrigé le compteur=1 mais je ne vois pas trop pourquoi il faudrait mettre un UPDATE dans ma requête ?

Ma table a une clé primaire et des clés étrangéres oui.

Avec le code que je vous ai fourni j'ai le message suivant de postgres :

ERROR:  syntax error at or near "$1"
LIGNE 1 :   $1 
            ^
REQUÊTE :   $1 
CONTEXTE : SQL statement in PL/PgSQL function "test2" near line 9


********** Erreur **********

ERROR: syntax error at or near "$1"
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "test2" near line 9

Il semblerait que cela vienne de

FOR table IN curseur

Du coup j'ai testé avec :

OPEN curseur;
FOR table IN curseur

avec un

END LOOP;
CLOSE curseur;

Mais c'est pas mieux...

Par ailleurs pourriez-vous me donner la différence entre un FETCH NEXT FROM curseur et un MOVE NEXT FROM curseur ?

Merci de votre aide.

rjuju

Administrateur

Re : UPDATE dans la sélection d'une sélection avec curseur

Si votre table a une clé primaire, il faut l'utiliser dans le WHERE de l'UPDATE pour que seule cette ligne soit mise à jour.
Vous êtes déjà dans un for, il n'est pas nécessaire de faire un FETCH. FETCH récupère un enregistrement alors que MOVE psse à l'enregistrement suivant.

table est un mot réservé, il faut donc utiliser un autre nom pour votre curseur.

Essayer plutôt comme ça (non testé il y a peut-être encore des erreurs)

CREATE OR REPLACE FUNCTION test()
  RETURNS integer AS
$BODY$
DECLARE
    curseur CURSOR FOR SELECT DISTINCT A,B,C FROM table_a_modif;
    matable record;
    matable2 record;
    compteur integer;
BEGIN 
    FOR matable IN curseur
    LOOP
        compteur=1;
	FOR matable2 IN SELECT cle_primaire,table_a_modif.A,table_a_modif.B,table_a_modif.C FROM table_a_modif WHERE table_a_modif.matable.A=A AND table_a_modif.B=matable.B AND table_a_modif.C=matable.C
	LOOP
		UPDATE table_a_modif SET D=compteur WHERE cle_primaire = matable2.cle_primaire;
		compteur=compteur+1;
        END LOOP;
    END LOOP;
        
    return 1;
END;
$BODY$
language plpgsql;

---

Julien.
https://rjuju.github.io/

Marc Cousin

Membre

Re : UPDATE dans la sélection d'une sélection avec curseur

Avec une window function, ça ne serait pas plus simple ?

SELECT *,row_number() over (partition by a,b,c) from test ;

---

Marc.

rjuju

Administrateur

Re : UPDATE dans la sélection d'une sélection avec curseur

exact, comme dit Marc les window function sont plus utiles pour ça, si vous êtes au moins en 8.4.
Cette requête devrait marcher :

UPDATE table_a_modif tbl SET tbl.d = sql.d
FROM (SELECT cle_primaire, row_number() OVER (PARTITION BY a,b,c) d FROM table_a_modif) sql
WHERE tbl.cle_primaire = sql.cle_primaire

Dernière modification par rjuju (20/08/2012 16:53:49)

---

Julien.
https://rjuju.github.io/

Geo-x

Membre

Re : UPDATE dans la sélection d'une sélection avec curseur

Je vous remercie à tous les deux pour votre aide toujours aussi rapide et efficace.

Malheureusement il semblerait que ma version de postgres (1.8.4 pourtant...) ne permette pas d'utiliser ce genre de requête :

ERROR:  syntax error at or near "OVER"
LIGNE 2 : ...LECT a,b,c, row_number() OVER (PART...

********** Erreur **********

ERROR: syntax error at or near "OVER"
État SQL :42601
Caractère : 111

Et là j'avoue que n'utilisant pas ce genre de requête, je vous fais à 100% confiance, mais je ne comprend plus vraiment ce que je fais puisque je ne connais pas la "window function".

Par ailleurs en essayant à nouveau la requête suivante avec mes paramètres :

CREATE OR REPLACE FUNCTION test()
  RETURNS integer AS
$BODY$
DECLARE
    curseur CURSOR FOR SELECT DISTINCT A,B,C FROM table_a_modif;
    matable record;
    matable2 record;
    compteur integer;
BEGIN 
    FOR matable IN curseur
    LOOP
        compteur=1;
	FOR matable2 IN SELECT cle_primaire,table_a_modif.A,table_a_modif.B,table_a_modif.C FROM table_a_modif WHERE table_a_modif.matable.A=A AND table_a_modif.B=matable.B AND table_a_modif.C=matable.C
	LOOP
		UPDATE table_a_modif SET D=compteur WHERE cle_primaire = matable2.cle_primaire;
		compteur=compteur+1;
        END LOOP;
    END LOOP;
        
    return 1;
END;
$BODY$
language plpgsql;

J'ai toujours le message :

ERROR:  syntax error at or near "$1"
LIGNE 1 :   $1 
            ^
REQUÊTE :   $1 
CONTEXTE : SQL statement in PL/PgSQL function "test2" near line 7


********** Erreur **********

ERROR: syntax error at or near "$1"
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "test2" near line 7

Alors j'ai bien quelques '' dans ma requête qui suit le 'curseur CURSOR FOR SELECT (...)' mais même si j'enlève tout ça ne focntionne toujours pas. J'ai également essayé de changer tous les noms de variable, rien n'y fait.

J'ai juste l'impression que la requête de ma focntion

FOR matable IN curseur

n'est pas accepté par postgres...

rjuju

Administrateur

Re : UPDATE dans la sélection d'une sélection avec curseur

Pour obtenir votre version de postgres, il faut faire un "select version();".

Le message n'a pas l'air cohérent par rapport à votre code, passez vous le nom de la table en paramètre ?
Je viens de tester en local avec cette fonction et cela marche très bien:

CREATE OR REPLACE FUNCTION test()
  RETURNS integer AS
$BODY$
DECLARE
    curseur CURSOR FOR SELECT DISTINCT A,B,C FROM windowtable;
    matable record;
    matable2 record;
    compteur integer;
BEGIN 
    FOR matable IN curseur
    LOOP
        compteur=1;
	FOR matable2 IN SELECT pk,windowtable.A,windowtable.B,windowtable.C FROM windowtable WHERE windowtable.A=matable.A AND windowtable.B=matable.B AND windowtable.C=matable.C
	LOOP
		UPDATE windowtable SET D=compteur where pk=matable2.pk;
		compteur=compteur+1;
        END LOOP;
    END LOOP;
        
    return 1;
END;
$BODY$
language plpgsql;

Edit: Effectivement, cela ne marche qu'à partir de la 8.4. Pour les  version 8.3 et inférieures, il faut remplacer le

FOR matable IN curseur

par

FOR matable IN SELECT DISTINCT a,b,c FROM matable

(et évidemment enlever la déclaration du cursor) et cela devrait fonctionner.

Dernière modification par rjuju (20/08/2012 17:52:16)

---

Julien.
https://rjuju.github.io/

Geo-x

Membre

Re : UPDATE dans la sélection d'une sélection avec curseur

Bonjour,

tout d'abord, je ne connaissais pas la requête :

select version()

Donc merci, il s'agit d'une version en 1.8.3.

Ensuite, il est vrai que si j'enlève le curseur ça fonctionne à merveille ! Un grand merci à vous deux pour votre rapidité et disponibilité (comme à chaque fois et ce n'est pas sur tous les forums pareils).

Très bonne journée à vous, pour moi, elle commence pas mal ;-)

Geo-x

Dernière modification par Geo-x (21/08/2012 12:08:37)

gleu

Administrateur

Re : UPDATE dans la sélection d'une sélection avec curseur

Pour informations, la version 1.8.3 n'a jamais existé (voir ftp://ftp-archives.postgresql.org/pub/source/).

---

Guillaume.

Geo-x

Membre

Re : UPDATE dans la sélection d'une sélection avec curseur

Oui Gleu vous avez tout à fait raison.

Je vous avais donné une mauvaise information avant que ne me donniez la fameuse requête :

 SELECT version() 

qui elle me répond :

"PostgreSQL 8.3.10, compiled by Visual C++ build 1400"

Pour info, vos réponses à ce sujet m'ont permis d'effectuer un certains nombres de manipulations de données supplémentaires.

Donc encore merci.

Geo-x