From: | george young <gry(at)ll(dot)mit(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | rename idx's with table; avoid confusing idx names? |
Date: | 2005-12-02 18:00:37 |
Message-ID: | 20051202130037.38a45fbf.gry@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-12-02 22:33:44 | Re: rename idx's with table; avoid confusing idx names? |
Previous Message | Mark Fenbers | 2005-12-02 17:44:27 | Re: Just 1 in a series... |