root/trunk/functions/verify_serial_sequences.sql

Revision 18, 1.4 kB (checked in by robert, 6 years ago)

verify sequences values against thier column values

Line 
1 create or replace function verify_serial_sequences(out v_tbl regclass, out v_col_said bigint, out v_seq_said bigint)
2 returns setof record
3 stable
4 as $$
5 declare
6     v_rec RECORD;
7     v_sql TEXT;
8 begin
9
10 for v_rec in select
11             n.nspname as schema_name,
12             c.relname as table_name,
13             c.oid as table_oid,
14             a.attname as column_name,
15             substring(d.adsrc from E'^nextval\\(''([^'']*)''(?:::text|::regclass)?\\)') as seq_name
16         from
17             pg_class c
18             join pg_attribute a on (c.oid=a.attrelid)
19             join pg_attrdef d on (a.attrelid=d.adrelid and a.attnum=d.adnum)
20             join pg_namespace n on (c.relnamespace=n.oid)
21         where
22             has_schema_privilege(n.oid,'USAGE')
23             and n.nspname not like 'pg!_%' escape '!'
24             and has_table_privilege(c.oid,'SELECT')
25             and (not a.attisdropped)
26             and d.adsrc ~ 'nextval'
27 loop
28
29     v_sql := 'select '||quote_literal(v_rec.table_oid::regclass)||', * from '||
30              '(select max('||v_rec.column_name||') from '||v_rec.schema_name||'.'||v_rec.table_name||') t, '||
31              '(select last_value from '||v_rec.schema_name||'.'||v_rec.seq_name||') s';
32
33     execute v_sql into v_tbl, v_col_said, v_seq_said;
34     return next;
35
36 end loop;
37
38 return;
39
40 end
41 $$ language plpgsql;
42
43 select * from verify_serial_sequences();
44
45 drop function verify_serial_sequences();
46
Note: See TracBrowser for help on using the browser.