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?
|
Список | 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 по дате отправления: