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 03/06/2024 16:38:17

Arnaud_CX
Membre

Lister les lignes de toutes les tables ayant une valeur spécifique

Bonjour à tous,

comme indiqué, je souhaite lister toutes les tables de mon schéma 'route' qui ont un champ 'PRJ_UUID' et dont la valeur de ce champ peut prendre différentes valeurs.

Après beaucoup de recherche j'arrive à faire la liste de toutes les tables avec ce champ (on ne rigole pas big_smile)

SELECT table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'route' AND column_name = 'PRJ_UUID'
ORDER BY table_name

mais quand j'intègre ce select dans un loop, ça se gâte. Voici le code que j'utilise :

DO
$do$
DECLARE
  sch text := 'route';
  tbl text;
BEGIN
  FOR tbl IN
    SELECT table_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = sch AND column_name = 'PRJ_UUID'
  LOOP
    EXECUTE format($$SELECT "id" FROM %I.%I WHERE "PRJ_UUID" = 'fc06279b-121b-463e-9409-87bb5eb6cc0b'
    $$, sch, tbl);
  END LOOP;
END
$do$;

je n'ai pas d'erreur mais je n'ai pas de résultat non plus, alors que je sais qu'il existe.

Pouvez-vous me dire ce qui cloche.
Merci par avance.

Hors ligne

#2 04/06/2024 04:38:44

rjuju
Administrateur

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Bonjour,

Le probleme ici est que EXECUTE format() va simplement executer la requete mais jeter le resultat.  La commande DO ne permet pas de retourner de resultat.  Vous pouvez soit utiliser RAISE NOTICE pour afficher une valeur retournee par le SELECT, soit utiliser une vraie fonction et dans ce cas retourner le resultat.

Hors ligne

#3 05/06/2024 10:29:49

Arnaud_CX
Membre

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Merci rjuju pour ta réponse.
Je ne connaissais pas RAISE NOTICE du coup j'ai fait des recherches et suis arrivé au code suivant.
C'est peut-être pas le meilleur niveau sémantique mais ça fonctionne big_smile

DO $$
DECLARE
    sch text := 'route';
    tbl record;
    row_id record;
    prj text := 'fc06279b-121b-463e-9409-87bb5eb6cc0b';
BEGIN
    FOR tbl IN
        SELECT table_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = sch AND column_name = 'PRJ_UUID' AND table_name not like 'SYS_%'
        ORDER BY table_name
    LOOP
        FOR row_id IN
            execute 'SELECT id
            FROM '|| sch || '."' || tbl.table_name ||'"
            WHERE "PRJ_UUID" = '''|| prj ||''''
        LOOP
            RAISE NOTICE 'Table: % - Id: %', tbl.table_name, row_id.id;
            END LOOP;
    END LOOP;
END $$;

Mon seul regret est de n'avoir pas réussi à avoir le résultat sous forme de tableau, mais je crois qu'il faut faire un CREATE TEMP TABLE.
C'est bien ça ?
Encore merci pour ton aide.

Dernière modification par Arnaud_CX (05/06/2024 10:59:54)

Hors ligne

#4 05/06/2024 15:30:57

rjuju
Administrateur

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Pour un resultat en forme de tableau il faut effectivement soit creer une table temporaire soit passer par une fonction qui renvoie des lignes, DO ne peut pas le faire.

Hors ligne

#5 25/06/2024 11:09:08

Arnaud_CX
Membre

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Bonjour,

j'avais mis le sujet de côté et après avoir fait des recherches sur les fonctions suite à ta réponse, je suis arrivé à un code qui ne renvoie pas d'erreur mais pas de résultat non plus.
Peux-tu me dire que qui est mal écrit ?
Merci par avance pour tes lumières big_smile

CREATE OR REPLACE FUNCTION list_rubric(anyelement)
RETURNS anyarray language plpgsql AS $$
DECLARE
    sch text := 'route';
    tbl record;
    row_id record;
    prj text := 'fc06279b-121b-463e-9409-87bb5eb6cc0b';
BEGIN
    FOR tbl IN
        SELECT table_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = sch AND column_name = 'PRJ_UUID' AND table_name not like 'SYS_%'
        ORDER BY table_name
    LOOP
        FOR row_id IN
            execute 'SELECT id
            FROM '|| sch || '."' || tbl.table_name ||'"
            WHERE "PRJ_UUID" = '''|| prj ||''''
        LOOP
            SELECT tbl.table_name, row_id.id;
        END LOOP;
    END LOOP;
END $$;

Hors ligne

#6 25/06/2024 16:53:45

rjuju
Administrateur

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Je pense qu'il faut plutot utiliser un RETURN NEXT a chqaue iteration, cf https://www.postgresql.org/docs/current … tures.html

Hors ligne

#7 27/06/2024 10:21:45

Arnaud_CX
Membre

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Merci rjuju pour tes énignes big_smile
J'ai complété le code au vu de ce que j'ai pu comprendre mais cela me renvoie une erreur par rapport au type de la variable tbl qui est record et qui n'est pas compatible avec next, mais si je la met en text elle n'est plus reconnu dans ma 2ème boucle.
Bref je sèche une fois de plus.
Merci par avance.

DROP TABLE IF EXISTS liste_rubrique_prj CASCADE;
CREATE TABLE liste_rubrique_prj(l_table_name TEXT, l_row_id TEXT);

CREATE OR REPLACE FUNCTION get_liste_rubrique_prj()
RETURNS SETOF liste_rubrique_prj
LANGUAGE plpgsql
AS $$
DECLARE
    sch text := 'route';
    tbl record;
    line record;
    prj text := 'fc06279b-121b-463e-9409-87bb5eb6cc0b';
BEGIN
    FOR tbl IN
        SELECT table_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = sch AND column_name = 'PRJ_UUID' AND table_name not like 'SYS_%'
        ORDER BY table_name
    LOOP
        FOR line IN
            EXECUTE 'SELECT id
            FROM '|| sch || '."' || tbl.table_name ||'"
            WHERE "PRJ_UUID" = '''|| prj ||''''
        LOOP
            INSERT INTO liste_rubrique_prj(l_table_name,l_row_id) VALUES (tbl.table_name,line.id);
        END LOOP;
    RETURN NEXT tbl;
    END LOOP;
    RETURN;
END $$;

SELECT * FROM get_liste_rubrique_prj();

Hors ligne

#8 25/09/2024 15:47:40

Arnaud_CX
Membre

Re : Lister les lignes de toutes les tables ayant une valeur spécifique

Bonjour,
j'ai mis le temps (j'ai fait d'autres choses quand même entre temps lol) mais j'ai réussi à faire la fonction que je voulais.
Ci-dessous, le code final de la fonction (les noms de variables ont changés) avec un exemple de select correspondant.
Merci.

CREATE OR REPLACE FUNCTION get_table_row_ids(sch text, prj text)
RETURNS TABLE (tablename text, row_id int) LANGUAGE plpgsql AS $$
BEGIN
    -- Boucle sur les tables du schéma spécifié
    FOR tablename IN
        SELECT table_name
        FROM information_schema.columns
        WHERE table_schema = sch 
          AND column_name = 'PRJ_UUID'
          AND table_name NOT LIKE 'SYS_%'
        ORDER BY table_name
    LOOP
        -- Requête dynamique pour récupérer les IDs
        FOR row_id IN
            EXECUTE format('SELECT id FROM %I.%I WHERE "PRJ_UUID" = %L', sch, tablename, prj)
        LOOP
            -- Retourner chaque ligne trouvée
            RETURN NEXT;
        END LOOP;
    END LOOP;
END $$;
SELECT * FROM get_table_row_ids('route', 'fc06279b-121b-463e-9409-87bb5eb6cc0b'); -- sch et prj à modifier

Hors ligne

Pied de page des forums