Re: Implementing automatic updating of primary keys...

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