Re: Merging item codes using referential integrity

> 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.