Message-ID: <3E12E207.9020504@klaster.net>
Date: Wed, 01 Jan 2003 13:41:43 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Implementing automatic updating of primary keys...
References: <200301161741.49024.mallah@trade-india.com>
In-Reply-To: <200301161741.49024.mallah@trade-india.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Rajesh Kumar Mallah. wrote:

>Hi we are working on re-structuring our database schemas and 
>intend to implement the functionality below at database level.
>
>consider a master table with following data.
>
>
>Table: profile_master
>------------
>
>id | username | password
>---|----------|----------
>1  |   u1     | p1
>2  |   u2     | p2
>
>id--> primary key not null.
>
>can some thing be done in the database level it self so that we do not have
>to keep modifying the mantainence programs as the number of tables referencing 
>master table grows?
>
>regds
>mallah.

If I understood well you want to change id in all tables from some value into another one and
no matter, how many these tables exist?

First - if your tables are created with "on update cascade", you can just change value on master table.

If you didn't create tables with this option and referencing key has the same name in all tables, 
it isn't still too difficult.

Everything you need is a function which finds all tables with field "id" and for each table performs:
update <table> set id=newvalue where id=oldvalue.

In plpgsql it will look something like:
create or replace function...
declare
 oldvalue alias for $1;
 newvalue alias for $2;
 tablename varchar;
begin
  for tablename in SELECT relname from pg_attribute join pg_class on (attrelid=oid) 
    where attname=''id'' and relkind='r';
  loop
    perform ''update '' || tablename '' set id='' || newvalue || '' where id='' || oldvalue;
  end loop;
end;

Many interesting find about database special tables you will find in 
Chapter 3. System Catalogs inside Postgresql documentation.

Regards,
Tomasz Myrta