YiPDNCza

1;
2do
3$$
4DECLARE	var_table_name character varying(1000);
5var_table_schema character varying(1000);
6var_index_name character varying(1000);
7var_query character varying(1000);
8var_is_debug boolean = false;
9begin
10
11	drop table if exists  temp_table_indexes_non_uniq;
12
13	create temp table if not exists temp_table_indexes_non_uniq  as
14    with tt_indexes as
15      (
16
17        select
18             n.nspname			"TableSchema",
19        c.relname as				"TableName",
20        a.relname as 				"IndexName",
21          b.indisunique 			"IsUnique",
22           n.nspname||c.relname|| a.relname "UniqName"
23
24      from
25        pg_class a
26          inner join
27        pg_index b
28          on
29        a.oid = b.indexrelid
30          inner join
31        pg_class c
32          on
33        b.indrelid = c.oid
34
35
36      JOIN
37          pg_catalog.pg_namespace n ON
38              n.oid        = a.relnamespace
39      where
40
41        a.relname not like 'pg_%'
42        and
43         n.nspname = 'tempdownload'
44         and
45         (
46          c.relname like 'safo%'
47          or
48              c.relname like 'ic%'
49          )
50      )
51      select "TableSchema",'"'||"TableName"||'"',"IndexName", 'drop index tempdownload."'||t1."IndexName"||'";' query
52      from
53          tt_indexes t1
54      where
55         t1."IsUnique" = false
56         and
57         exists (select 1  from tt_indexes t2 where t1."TableName" = t2."TableName" and t2."IsUnique" = true)
58         ;
59
60
61
62
63
64
65    for var_query in	select query from temp_table_indexes_non_uniq loop
66
67        raise  notice 'Query to execute : %',var_query;
68
69		if     (var_is_debug=false) THEN
70	        execute var_query;
71        end if;
72
73
74	end loop;
75end;
76$$
77language 'plpgsql';