In my last post I showed you a simple way to check to see if a constraint already existed in PostgreSQL. Now I want to show you how to do the same thing for an index.
Here’s the code but keep in mind that it makes the assumption that everything is in the public schema.
12345678910111213141516171819202122
CREATEORREPLACEFUNCTIONcreate_index_if_not_exists(t_nametext,i_nametext,index_sqltext)RETURNSvoidAS$$DECLAREfull_index_namevarchar;schema_namevarchar;BEGINfull_index_name=t_name||'_'||i_name;schema_name='public';IFNOTEXISTS(SELECT1FROMpg_classcJOINpg_namespacenONn.oid=c.relnamespaceWHEREc.relname=full_index_nameANDn.nspname=schema_name)THENexecute'CREATE INDEX '||full_index_name||' ON '||schema_name||'.'||t_name||' '||index_sql;ENDIF;END$$LANGUAGEplpgsqlVOLATILE;