Hi,
Here is the query in PostgreSQL finds the list of tables and it's related sequences.
postgres=# SELECT TAB_SEQ_LIST.TABLE,TAB_SEQ_LIST.attname AS Columnname,TAB_SEQ_LIST.nspname AS Schemaname,case when position((nspname||'.')::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)=0 and length(TAB_SEQ_LIST.sequence_def)>=1 then 'public.'||sequence_def else (select nspname from pg_class,pg_namespace where relkind='S' and relnamespace=pg_namespace.oid and relname=TAB_SEQ_LIST.sequence_def and pg_namespace.nspname=TAB_SEQ_LIST.nspname)::text||'.'||TAB_SEQ_LIST.sequence_def end as sequence_name FROM ( SELECT a.attrelid::regclass as table,attrelid ,attname,nspname,(select trim(both '''' from rtrim(ltrim(regexp_replace(regexp_replace(regexp_replace(case when substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) like 'nextval(%::regclass)' then substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) end,'''''','''','g') ,'nextval',''),'::regclass','')::text,'(')::text,')')) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as sequence_def FROM pg_catalog.pg_attribute a inner join pg_class on a.attrelid=pg_class.oid inner join pg_namespace on relnamespace=pg_namespace.oid and nspname !~'^pg_catalog|^information_schema|^pg_toast|^pg_temp' AND a.attnum >0 AND NOT a.attisdropped ORDER BY a.attnum ) AS TAB_SEQ_LIST WHERE TAB_SEQ_LIST.sequence_def is not null and nspname !~ '^pg_catalog|^information_schema|^pg_toast|^pg_temp';
table | columnname | schemaname | sequence_name
----------------+------------+------------+---------------
test | t | public | public.seq
sample.seqtest | t | sample | public.seq
--Dinesh
Here is the query in PostgreSQL finds the list of tables and it's related sequences.
postgres=# SELECT TAB_SEQ_LIST.TABLE,TAB_SEQ_LIST.attname AS Columnname,TAB_SEQ_LIST.nspname AS Schemaname,case when position((nspname||'.')::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)>0 then TAB_SEQ_LIST.sequence_def when position('.'::varchar in TAB_SEQ_LIST.sequence_def)=0 and length(TAB_SEQ_LIST.sequence_def)>=1 then 'public.'||sequence_def else (select nspname from pg_class,pg_namespace where relkind='S' and relnamespace=pg_namespace.oid and relname=TAB_SEQ_LIST.sequence_def and pg_namespace.nspname=TAB_SEQ_LIST.nspname)::text||'.'||TAB_SEQ_LIST.sequence_def end as sequence_name FROM ( SELECT a.attrelid::regclass as table,attrelid ,attname,nspname,(select trim(both '''' from rtrim(ltrim(regexp_replace(regexp_replace(regexp_replace(case when substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) like 'nextval(%::regclass)' then substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) end,'''''','''','g') ,'nextval',''),'::regclass','')::text,'(')::text,')')) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as sequence_def FROM pg_catalog.pg_attribute a inner join pg_class on a.attrelid=pg_class.oid inner join pg_namespace on relnamespace=pg_namespace.oid and nspname !~'^pg_catalog|^information_schema|^pg_toast|^pg_temp' AND a.attnum >0 AND NOT a.attisdropped ORDER BY a.attnum ) AS TAB_SEQ_LIST WHERE TAB_SEQ_LIST.sequence_def is not null and nspname !~ '^pg_catalog|^information_schema|^pg_toast|^pg_temp';
table | columnname | schemaname | sequence_name
----------------+------------+------------+---------------
test | t | public | public.seq
sample.seqtest | t | sample | public.seq
--Dinesh
Comments
Post a Comment