From jasiek@klaster.net Thu Jan 16 12:37:34 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 51503476169 for ; Thu, 16 Jan 2003 07:37:34 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 531BC475CEE for ; Thu, 16 Jan 2003 07:37:31 -0500 (EST) Received: from klaster.net (pe48.krakow.cvx.ppp.tpnet.pl [213.76.40.48]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id C000F2B848; Thu, 16 Jan 2003 13:34:17 +0100 (CET) Message-ID: <3E12E207.9020504@klaster.net> Date: Wed, 01 Jan 2003 13:41:43 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US; rv:1.2b) Gecko/20021016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Rajesh Kumar Mallah." 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 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/260 X-Sequence-Number: 11125 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 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 From mallah@trade-india.com Thu Jan 16 12:06:43 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4B1BE476333 for ; Thu, 16 Jan 2003 07:06:43 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 7FE68476173 for ; Thu, 16 Jan 2003 07:06:39 -0500 (EST) Received: (qmail 1629 invoked from network); 16 Jan 2003 12:01:27 -0000 Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 16 Jan 2003 12:01:27 -0000 From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: pgsql-sql@postgresql.org Subject: Implementing automatic updating of primary keys... Date: Thu, 16 Jan 2003 17:41:49 +0530 User-Agent: KMail/1.5 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200301161741.49024.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/259 X-Sequence-Number: 11124 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. table t1 ----------- id | service_id ---|------------ 1 | 1 2 | 10 table t2 -------------- id | rfi_id ---|----------- 1 | 1001 2 | 23 there can be many such tables that have foreign key id which is referencing the master table test_master column "id". what we want is when some ids become redundant we have to merge two ids , we want that thru out the system the occurance of the old primary key shud be removed and replaced with the new id. so if id 2 were to be mergered to id 1 then the tables shud look like: Table: profile_master ------------ id | username | password ---|----------|---------- 1 | u1 | p1 id--> primary key not null. table t1 ----------- id | service_id ---|------------ 1 | 1 1 | 10 table t2 -------------- id | rfi_id ---|----------- 1 | 1001 1 | 23 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. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. From mallah@trade-india.com Thu Jan 16 14:23:20 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CB9B2475FD5 for ; Thu, 16 Jan 2003 09:23:20 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 39A03476169 for ; Thu, 16 Jan 2003 09:23:15 -0500 (EST) Received: (qmail 2685 invoked from network); 16 Jan 2003 14:17:55 -0000 Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 16 Jan 2003 14:17:55 -0000 From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: Tomasz Myrta Subject: Re: Implementing automatic updating of primary keys... Date: Thu, 16 Jan 2003 19:58:17 +0530 User-Agent: KMail/1.5 Cc: pgsql-sql@postgresql.org References: <200301161741.49024.mallah@trade-india.com> <3E12E207.9020504@klaster.net> In-Reply-To: <3E12E207.9020504@klaster.net> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200301161958.17064.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/261 X-Sequence-Number: 11126 yes you got my problem rightly. If i use "on update cascade" approach still there is problem. If i attempt to update the key in master table it wont be allowed becoz of temporary violation of PRIMARY KEY CONSTRAINT. becoz 1 is also existing in the master table. update profile_master set id=1 where id=2 will not be accepted. regds mallah. On Wednesday 01 January 2003 06:11 pm, Tomasz Myrta wrote: > 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
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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. From bhuvansql@myrealbox.com Mon Jan 20 09:35:23 2003 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8948E475FD4 for ; Mon, 20 Jan 2003 04:35:23 -0500 (EST) Received: from Gateway.bksys.co.in (unknown [61.1.200.112]) by postgresql.org (Postfix) with ESMTP id 2D6B9475E77 for ; Mon, 20 Jan 2003 04:35:17 -0500 (EST) Received: from bksys.co.in (mail.bksys.co.in [192.168.1.1]) by Gateway.bksys.co.in (8.11.6/8.11.6) with ESMTP id h0K9aMu16081; Mon, 20 Jan 2003 15:06:22 +0530 Received: from Bhuvan.bksys.co.in (Bhuvan.bksys.co.in [192.168.1.71]) by bksys.co.in (8.11.6/8.8.5) with ESMTP id h0K9ZMS23394; Mon, 20 Jan 2003 15:05:22 +0530 Date: Mon, 20 Jan 2003 15:25:36 +0530 (IST) From: Bhuvan A X-X-Sender: bhuvan@Bhuvan.bksys.co.in To: "Rajesh Kumar Mallah." Cc: pgsql-sql@postgresql.org Subject: Re: Implementing automatic updating of primary keys... In-Reply-To: <200301161741.49024.mallah@trade-india.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/295 X-Sequence-Number: 11160 > > there can be many such tables that have foreign key id which is referencing > the master table test_master column "id". what we want is when some ids become ^^^^^^^^^^^ It should be profile_master, right? > redundant we have to merge two ids , we want that thru out the system the > occurance of the old primary key shud be removed and replaced with the new id. > Definitely it can be done in the database level. Trigger a function before delete on the primary table (profile_master) and update the foreign tables with appropriate id and so the redundant id can be deleted from the primary table. regards, bhuvaneswaran