From: "Andrus Moor" <nospameetasoftnospam@online.ee>
Subject: Re: Merging item codes using referential integrity
Date: Sun, 3 Apr 2005 13:50:05 +0300
Organization: Hub.Org Networking Services
Lines: 86
Message-ID: <d2ohp3$pt5$1@news.hub.org>
References: <d21pqg$25ni$1@news.hub.org> <42492A1D.9060404@archonet.com>
To: pgsql-sql@postgresql.org

> Andrus Moor wrote:
>> I have item table and a lot of child tables where the items are used.
>> I want to merge two item codes into single item in all tables.
>> It is not nice to write a lot of separate UPDATE statements for each 
>> table.
>> So I want to utilize REFERENCES clause for merging.
>>
>> I tried the following code but got duplicate key error in UPDATE
>> statement.
>>
>> Any idea how to impement this?
>>
>> CREATE TABLE parent ( code CHAR(10) PRIMARY KEY  );
>
>> BEGIN;
>> -- Direct Postgres to update all child tables. This causes error.
>> UPDATE parent SET code='1' WHERE code='2';
>> -- Remove duplicate row
>
> That's the problem - you can't have a duplicate row at *any* time with a 
> primary key. The UNIQUE constraint is instant and can't be deferred (at 
> least, not yet).
>
> However, in this case I would simply write a function:
>
> CREATE FUNCTION merge_all(char(10), char(10) AS '
>   UPDATE table_1 SET col_1=$2 WHERE col1=$1;
>   UPDATE table_2 SET col_2=$2 WHERE col2=$2;
>   ...etc...
> ' LANGUAGE SQL;
>
> Then: SELECT merge_all('OLD_VAL','NEW_VAL') for each value (you could even 
> join to your "parent" table if all the values are in there). All the 
> updates in the function take place in the same transaction, so if there 
> are any problems then all changes will be rolled back.

Richard,

thank you.
Is is possible to determine table_1 , col_1  etc values automatically.
I have some hundreds of referential intgrety constraints which are changing. 
So I must write and maintains hundres of additional lines of code which 
duplicates existing referential integrity information.

I'm researching the following method:

Input:

Master table name $master and two its primary key values  $value1 and
$value2

Output:

1. All $value2 field values in child tables are update to $value1
2. $value2 record is deleted from $master table

Algorithm:

SELECT
  childtablename,
  childfieldname
FROM pg_referentialinfo
WHERE pg_referentialinfo.mastertable=$master
INTO CURSOR childs;

BEGIN TRANSACTION;
SCAN FOR ALL childs RECORDS;
  UPDATE  (childs.childtablename)  set (childs.childfieldname)=$value2
       WHERE EVAL(childs.childfieldname)=$value1;
ENDSCAN;

SELECT
  primarykeyfieldname
FROM pg_tables
WHERE pg_tables.tablename=$master
INTO CURSOR mfield;

DELETE FROM $master WHERE EVAL(mfield.primarykeyfieldname)=$value2;
COMMIT;


How to implement SCAN FOR ALL childs RECORDS in PostgreSQL ?

Andrus.