From: Tomasz Myrta <jasiek@klaster.net>
To: Rajesh Kumar Mallah. <mallah@trade-india.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Implementing automatic updating of primary keys...
Date: Wed, 01 Jan 2003 13:41:43 +0100
Message-ID: <3E12E207.9020504@klaster.net> (raw)
In-Reply-To: <200301161741.49024.mallah@trade-india.com>
References: <200301161741.49024.mallah@trade-india.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, mallah@trade-india.com
Subject: Re: Implementing automatic updating of primary keys...
In-Reply-To: <3E12E207.9020504@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox