Date: Fri, 2 Dec 2005 13:00:37 -0500
From: george young <gry@ll.mit.edu>
To: pgsql-sql@postgresql.org
Subject: rename idx's with table; avoid confusing idx names?
Message-Id: <20051202130037.38a45fbf.gry@ll.mit.edu>
Reply-To: gry@ll.mit.edu
Organization: MIT Lincoln Laboratory
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
After tearing out some hair over the following sequence of events:

[a few weeks ago]
   alter table foo rename to old_foo;
   create table foo(<somewhat different schema>);
   insert into foo select blahblahblah from old_foo;

[today]
   cluster foo_pkey on foo;
   ERROR:  "foo_pkey" is not an index for table "foo"
   What?????  Why does \d say the primary key idx is foo_pkey1 ????

[light dawns]
   Aha! "alter table rename to" did not rename the table's indexes!

I put together a plpgsql function to rename a table and it's indexes
correspondingly[see below].  I would like to know:

  Is there a more robust/portable/clear way to do this?
  Is this a bad idea for some subtle reason?
  Is there any way to get a less cumbersome interface than "select rename_table_and_indexes('foo','old_foo')?
  Does this look useful enough for me to package more formally? 

-- George Young

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
CREATE or REPLACE FUNCTION rename_table_and_indexes(old_name text, new_name text) returns void AS $$
declare
   prefix_len integer;
   r record;
begin
   prefix_len = length(old_name);
   for r in select indexrelname from pg_stat_user_indexes where relname=old_name loop
      execute 'alter index ' || r.indexrelname || ' rename to ' || quote_ident(new_name) || substr(r.indexrelname, prefix_len + 1);
      raise NOTICE 'renamed index % to %', r.indexrelname, new_name || substr(r.indexrelname, prefix_len + 1);
      end loop;

   execute 'alter table ' || quote_ident(old_name) || ' rename to ' || quote_ident(new_name);
   raise NOTICE 'alter table % rename to %', old_name, new_name;
end;
$$
    LANGUAGE plpgsql;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)