Re: Help with SQL - Mailing list pgsql-general

> 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
storedprocedures.  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
generatedfiles 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.