Forums PostgreSQL.fr

Geo-x

Membre

Lister noms de colonnes associées à une séquence

Bonjour @tous.

J'essaie de construire une requête permettant de lister les colonnes concernées par une séquence.

Je sais lister les séquences :

SELECT * FROM pg_class c WHERE c.relkind = 'S';

mais je n'arrive pas à trouver l'endroit ou je peux trouver la colonne associée.

Auriez-vous une idée ?

Par avance merci.

Geo-x

rjuju

Administrateur

Re : Lister noms de colonnes associées à une séquence

C'est un problème un peu complexe, car il n'est pas nécessaire s'associer une séquence à une colonne.

Néanmoins, dans le cas le plus simple (création d'une colonne de type serial ou bigserial), vous pouvez trouver l'information dans pg_depend. Par exemple :

SELECT seq.relname as sequence, t.relname as table_name, a.attname as column_name
FROM pg_class seq
JOIN pg_depend d ON seq.oid = d.objid
JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a on a.attrelid = t.oid AND a.attnum = d.refobjsubid
WHERE seq.relkind = 'S'
AND d.refobjsubid > 0;

(requête pas vraiment testée, mais c'est l'idée)

---

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

Geo-x

Membre

Re : Lister noms de colonnes associées à une séquence

Bonjour Julien et merci de votre réponse.

Quand vous dites qu'il n'est pas forcément nécessaire d'associer une séquence à une colonne, je ne comprends pas, à quoi peut être rattachée une séquence autre que des colonnes ?

rjuju

Administrateur

Re : Lister noms de colonnes associées à une séquence

Rien ne vous empêche de créer une séquence, et de l'utiliser pour renseigner un champ d'une ou plusieurs table.  Dans ce cas la séquence n'aura pas de dépendance avec des colonnes.  De plus si vous créez une séquence, et que vous modifier la clause default d'une colonne pour utiliser la séquence, la dépendance ne sera pas non plus présente.  Donc la requête ci dessus ne permet de détecter que les séquences créées parce qu'un type (big)serial a été utilisé.

---

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

Geo-x

Membre

Re : Lister noms de colonnes associées à une séquence

Ah oui d'accord, je n'avais pas pensé à ces possibilités, merci du partage de savoir.

Si je résume, il n'est donc pas possible de détecter les colonnes impactées par une séquence, à moins qu'elles ne soient directement créées en serial.

Merci beaucoup pour toutes ces précisions Julien.

Geo-x

jmarsac

Membre

Re : Lister noms de colonnes associées à une séquence

Bonjour,
Un peu tard peut-être, mais voici le code d'une fonction qui permet de mettre à jour (et donc de lister) les séquences utilisée comme valeur par défaut par les tables du ou des schémas indiqués. Deux contraintes :
- la séquence est dans le même schéma que la table qui l'utilise
- la séquence est utilisée par une seule table

--
-- Met à jour les séquences définissant les valeurs par défaut des tables du ou des 
-- schemas correspondant au pattern indiqué.
-- suppose que la séquence est définie dans le même schéma que la table qui l'utilise
-- suppose qu'une seule table utilise chaque séquence
--
-- usage: select update_my_sequences('sig%'); pour mettre à jour toutes les séquences
--        utilisées comme valeur par défaut par les tables des schémas dont le
--        nom commence par 'sig'
--
-- Traite les séquences qui aurait été définies comme valeur par défaut "après coup"
--
create or replace function update_my_sequences(v_schemas character varying)
returns integer as
$$
declare
v_nb integer := 0;
v_max bigint := 0;
v_sql varchar := '';
v_schemaname varchar := '';
v_rec1 record;

begin

FOR v_schemaname IN SELECT schema_name FROM information_schema.schemata WHERE schema_name like v_schemas LOOP
	RAISE info '=================================================================';
	raise info 'Schéma %',v_schemaname;
	v_sql := FORMAT('SELECT regexp_replace(column_default, ''nextval\(''''([a-z0-9_]+)''''::regclass\)'',''\1'') AS sequence_name,table_schema,table_name,column_name,data_type,column_default from information_schema.columns WHERE table_schema LIKE %L AND column_default LIKE ''nextval%%''',v_schemaname);
	RAISE info '-----------------------------------------------------------------';
	--raise info '%', v_sql;
	FOR v_rec1 IN EXECUTE v_sql LOOP
		EXECUTE FORMAT('SELECT max(%I) FROM %I.%I', v_rec1.column_name,v_rec1.table_schema , v_rec1.table_name) INTO v_max; 
		RAISE INFO 'SEQUENCE=%, MAX(%)=%',v_rec1.sequence_name,v_rec1.table_schema || '.' || v_rec1.table_name || '.' || v_rec1.column_name, v_max;
		if v_max is not null then
			v_sql := format('SELECT setval(%L,',v_rec1.table_schema || '.' || v_rec1.sequence_name) || v_max || ')';
			raise info '%', v_sql;
			execute v_sql;
			v_nb := v_nb + 1;
		end if;
	END LOOP;
END LOOP;
RETURN v_nb;

end;
$$
language plpgsql;

Geo-x

Membre

Re : Lister noms de colonnes associées à une séquence

Merci du retour jmarsac :-)