rename idx's with table; avoid confusing idx names?

Поиск
Список
Период
Сортировка
От george young
Тема rename idx's with table; avoid confusing idx names?
Дата
Msg-id 20051202130037.38a45fbf.gry@ll.mit.edu
обсуждение исходный текст
Ответы Re: rename idx's with table; avoid confusing idx names?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-sql
[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
selectblahblahblah 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
primarykey 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
geta less cumbersome interface than "select rename_table_and_indexes('foo','old_foo')? Does this look useful enough for
meto 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)


В списке pgsql-sql по дате отправления:

Предыдущее
От: Mark Fenbers
Дата:
Сообщение: Re: Just 1 in a series...
Следующее
От: Tom Lane
Дата:
Сообщение: Re: rename idx's with table; avoid confusing idx names?