pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Merging item codes using referential integrity
3+ messages / 2 participants
[nested] [flat]

* Merging item codes using referential integrity
@ 2005-03-25 19:46  Andrus Moor <nospameetasoftnospam@online.ee>
  0 siblings, 1 reply; 3+ messages in thread

From: Andrus Moor @ 2005-03-25 19:46 UTC (permalink / raw)
  To: pgsql-sql

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  );

CREATE TABLE orders ( anyfield CHAR(10) REFERENCES parent ON UPDATE 
CASCADE );
CREATE TABLE invoices ( anyotherfield CHAR(10) REFERENCES parent ON UPDATE 
CASCADE );
-- ... a lot of more child tables with different table and field names 
but -- always with same REFERENCES clause.

INSERT INTO parent VALUES ('1');
INSERT INTO parent VALUES ('2');
INSERT INTO orders VALUES ('1');
INSERT INTO invoices VALUES ('1');
INSERT INTO orders VALUES ('2');
INSERT INTO invoices VALUES ('2');

BEGIN;
-- Direct Postgres to update all child tables. This causes error.
UPDATE parent SET code='1' WHERE code='2';
-- Remove duplicate row
CREATE TABLE parent AS
  SELECT * FROM parent
  GROUP BY CODE ;
COMMIT;



Andrus. 





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Merging item codes using referential integrity
@ 2005-03-29 10:12  Richard Huxton <dev@archonet.com>
  parent: Andrus Moor <nospameetasoftnospam@online.ee>
  0 siblings, 1 reply; 3+ messages in thread

From: Richard Huxton @ 2005-03-29 10:12 UTC (permalink / raw)
  To: Andrus Moor <nospameetasoftnospam@online.ee>; +Cc: pgsql-sql

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 Huxton
   Archonet Ltd



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Merging item codes using referential integrity
@ 2005-04-03 10:50  Andrus Moor <nospameetasoftnospam@online.ee>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Andrus Moor @ 2005-04-03 10:50 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-04-03 10:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-25 19:46 Merging item codes using referential integrity Andrus Moor <nospameetasoftnospam@online.ee>
2005-03-29 10:12 ` Richard Huxton <dev@archonet.com>
2005-04-03 10:50   ` Andrus Moor <nospameetasoftnospam@online.ee>

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