Help with SQL

On May 27, 2018, at 6:42 PM, anand086 <anand086@gmail.com> wrote:

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?

Is using a stored procedure a required? To do so would require “dynamic sql” but imho that’s not the best use of stored procedures. Perhaps you could use your “with ids” sql to get the list of required indices with names, columns etc, place those values (sed, awk, perl, python, even sql) into versions of your create index code and stick those generated files in hopefully the same source code repository which has the definition of the parent and children tables. They will likely all need maintenace at the same time.