Search Postgresql Archives

Help with SQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux