Hi,
I have a requirement of writing plpgsql function to create partial indexes
on child tables if it exists on parent table. The function will have
schemname, childtablename, tableowner as input.
I am using the below code to identify the indexname and index definition
from parent table --
```
with idx as
(select indexrelid::regclass indexname, indisunique,
indisprimary from pg_index where indrelid in
(select oid from pg_class where relname in (select
tablename from pg_indexes where tablename='test_booking')
)
and indpred is not null
)
select idxs.tablename, idxs.indexname, idxs.indexdef,
idx.indisunique from pg_indexes as idxs join idx on
idxs.indexname=split_part(idx.indexname::text , '.' ,2)
```
Suppose the indexdef is on parent table is --
CREATE UNIQUE INDEX uniq_test_booking_1 ON demo.test_booking USING btree
(col1,col2 ,col3, col4,col5, col6) WHERE ((col4 IS NOT NULL) AND (col6 IS
NOT NULL))
Now, what I am trying to achieve is to create and execute the below sql,
wherein I replace the indexname with uniq_<child_table_name>_<randomtext>
and tablename with the childtablename part of function input.
CREATE UNIQUE INDEX uniq_test_booking_20180527_gdhsd ON
demo.test_booking_20180527 USING btree (col1,col2 ,col3, col4,col5, col6)
WHERE ((col4 IS NOT NULL) AND (col6 IS NOT NULL))
Using substring I am trying to break the SQL statement in 2 and then later
concatenate it.
The first part is substring(idxrec.indexdef from 0 for 21); --> output is
"create unique index " statement.
and for the 2nd part, starting for USING until the end. But I am unable to
get the 2nd part of sql.
```
if idxrec.indisunique='t' then
SELECT substr(concat(md5(random()::text), md5(random()::text)),
0,
7) into var;
idxname:='uniq_'||idxrec.tablename||'_'||var;
raise notice 'Index name will be %', idxname;
createStmts1:=substring(idxrec.indexdef from 0 for 21); -->
gives me
the "create unique index"
raise notice 'String1 %', createStmts1;
createStmts2:=
raise notice 'String2 %', createStmts2;
```
Is this the correct way? Any better suggestion?
How can I achieve this?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
