Annonce
Pour sa 21ème année, l’association GeoRezo a toujours besoin de vous !
10€ = 1 mois de frais bancaires ; 15€ = 12 mois de nom de domaine ; 30€ = 1 semaine de location des serveurs …
Retrouver nos membres bienfaiteurs
#1 Mon 27 February 2023 11:45
- image95
- Participant assidu
- Date d'inscription: 6 Sep 2014
- Messages: 259
Répéter opérations sur 50 tables d'un même schema?
Bonjour,
Je suis débutant avec Postgresql. J'ai 50 tables dans un schéma "ign" (schéma autre que public).
Je souhaite pour chacune de ces 50 tables :
- Ajouter un prefixe au nom de la table : "IGN_bdTopo_"
- Ajouter un suffixe au nom de la table : "_V1"
- créer une nouvelle colonne "date" de type date. Et peupler ce champ avec la valeur : 15/06/2021
- créer une nouvelle colonne "source" de type varchar (longueur 50). Et peupler ce champ avec la valeur : 'ign'.
- déplacer tous les éléments de ces 50 tables (including all). du schema "ign" vers le schema "ign_v2". Que ce soit les donnée, les contraintes, les indexes.
Si une personne pense pouvoir m'aider ? Merci beaucoup.
Hors ligne
#2 Mon 27 February 2023 14:33
- tweaxy
- Participant actif
- Lieu: Abbeville
- Date d'inscription: 27 Dec 2018
- Messages: 76
Re: Répéter opérations sur 50 tables d'un même schema?
Bonjour,
Une extension e-maj qui répondrait à cela existe à priori : https://github.com/dalibo/emaj et https://emaj.readthedocs.io/fr/latest/ (Merci jmarsac)
N'ayant pas testé cette extension, vous pouvez également passer par une deux functions (peut être existe-t-il une méthode plus simple).
Code:
create or replace function create_trigger() returns integer as $$
declare
v_count integer;
v_table record;
begin
v_count := 0;
FOR v_table IN
SELECT n.nspname AS schemaname,
c.relname AS tablename
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind IN ('r') AND n.nspname = 'ign'
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' ADD COLUMN date date';
EXECUTE 'UPDATE ' || quote_ident(v_table.schemaname) ||'.'|| quote_ident(v_table.tablename) || ' SET date = ''15/06/2021'' ';
EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' ADD COLUMN source varchar(50)';
EXECUTE 'UPDATE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' SET source = ''ign'' ';
EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' RENAME TO ign_bdtopo_' || quote_ident(v_table.tablename)||'_v1';
v_count := v_count + 1;
END LOOP;
RETURN v_count;
end
$$ language 'plpgsql';
create or replace function move_table() returns integer as $$
declare
v_count integer;
v_table record;
begin
v_count := 0;
FOR v_table IN
SELECT n.nspname AS schemaname,
c.relname AS tablename
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind IN ('r') AND n.nspname = 'ign'
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' SET SCHEMA ign_v2' ;
END LOOP;
RETURN v_count;
end
$$ language 'plpgsql';
select create_trigger();
select move_table();Les noms de table sont ici en minuscule.
Si vous voulez impérativement nommer vos tables avec des majuscules (plutôt déconseillé, cela évite de mettre des "" dans les requêtes SQL), il vous faudra adapter un petit peu ce code avec des "".
Ce code fonctionne de mon côté (pg version 9.6)
Cordialement,
Léandre BERON
Hors ligne
#3 Tue 28 February 2023 13:51
- Nicolas Ribot
- Membre
- Lieu: Toulouse
- Date d'inscription: 9 Sep 2005
- Messages: 1554
Re: Répéter opérations sur 50 tables d'un même schema?
Bonjour,
Sinon sans utiliser de fonctions, ce qui se fait souvent pour de l'admin comme celle-la est d'écrire une requête SQL qui fabrique du SQL, qui sera ensuite lancé.
psql permet de lancer le résultas de requêtes SQL comme nouvelles requetes (\gexec).
Par exemple, pour renommer des tables: (ce 'format' peut etre complété pour ajouter toutes les modifs que vous voulez sur les tables)
Code:
select format('alter table ign.%1$I rename to ign_bdtopo_%1$I_v1;', table_name) as query
from information_schema.tables
where table_schema = 'ign';Nicolas
En ligne
