Forums PostgreSQL.fr

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 )

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.

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.

---

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

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

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)

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.

---

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

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

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 $$;

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

---

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

Arnaud_CX

Membre

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

Merci rjuju pour tes énignes
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();