Help with SQL

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