Me gustaría seleccionar todas las secuencias en la base de datos, obtener el esquema de secuencia, tabla dependiente, el esquema de una tabla, columna dependiente.
He intentado la siguiente consulta:
SELECT ns.nspname AS sequence_schema_name, s.relname AS sequence_name, t_ns.nspname AS table_schema_name, t.relname AS table_name, a.attname AS column_name, s.oid, s.relnamespace, d.*, a.* FROM pg_class s JOIN pg_namespace ns ON ns.oid = s.relnamespace left JOIN pg_depend d -- ON d.objid = s.oid --TO FIX??? AND d.classid = 'pg_class'::regclass --TO FIX??? AND d.refclassid = 'pg_class'::regclass --TO FIX??? left JOIN pg_class t ON t.oid = d.refobjid --TO FIX??? left JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid left JOIN pg_namespace t_ns ON t.relnamespace = t_ns.oid WHERE s.relkind = 'S' ;Desafortunadamente, esta consulta no funciona al 100%. La consulta filtra algunas secuencias.
Lo necesito para un procesamiento posterior (después de la restauración de datos en diferentes ENV, necesito encontrar el valor de columna máximo y establecer la secuencia en MAX+1).
¿Alguien podría ayudarme?
La siguiente consulta debería funcionar:
create table foo(id serial, v integer); create table boo(id_boo serial, v integer); create sequence omega; create table bubu(id integer default nextval('omega'), v integer); select sn.nspname as seq_schema, s.relname as seqname, st.nspname as tableschema, t.relname as tablename, at.attname as columname from pg_class s join pg_namespace sn on sn.oid = s.relnamespace join pg_depend d on d.refobjid = s.oid join pg_attrdef a on d.objid = a.oid join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum join pg_class t on t.oid = a.adrelid join pg_namespace st on st.oid = t.relnamespace where s.relkind = 'S' and d.classid = 'pg_attrdef'::regclass and d.refclassid = 'pg_class'::regclass; ┌────────────┬────────────────┬─────────────┬───────────┬───────────┐ │ seq_schema │ seqname │ tableschema │ tablename │ columname │ ╞════════════╪════════════════╪═════════════╪═══════════╪═══════════╡ │ public │ foo_id_seq │ public │ foo │ id │ │ public │ boo_id_boo_seq │ public │ boo │ id_boo │ │ public │ omega │ public │ bubu │ id │ └────────────┴────────────────┴─────────────┴───────────┴───────────┘ (3 rows) Para llamar a funciones relacionadas con la secuencia, puede usar la columna s.oid . Para este caso, es un identificador único de secuencia oid. Necesitas enviarlo a regclass .
Un script para su solicitud puede parecerse a:
do $$ declare r record; max_val bigint; begin for r in select s.oid as seqoid, at.attname as colname, a.adrelid as reloid from pg_class s join pg_namespace sn on sn.oid = s.relnamespace join pg_depend d on d.refobjid = s.oid join pg_attrdef a on d.objid = a.oid join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum where s.relkind = 'S' and d.classid = 'pg_attrdef'::regclass and d.refclassid = 'pg_class'::regclass loop -- probably lock here can be safer, in safe (single user) maintainance mode -- it is not necessary execute format('lock table %s in exclusive mode', r.reloid::regclass); -- expect usual one sequnce per table execute format('select COALESCE(max(%I),0) from %s', r.colname, r.reloid::regclass) into max_val; -- set sequence perform setval(r.seqoid, max_val + 1); end loop; end; $$ Nota: Usar %s para el nombre de la tabla o el nombre de la secuencia en la función de format es seguro, porque la conversión del tipo Oid al tipo regclass genera una cadena segura (el esquema se usa cuando es necesario cada vez, el escape se usa cuando es necesario cada vez) .