Hi folks,
I have multiple tables where some of the rows need sometimes to be
duplicated, but where the copy record has in one column a different
value. I have an idea how I could realize this via functions, but
probably you have another suggestion?
Example table:
CREATE TABLE "FooHolding" (
"CoosOfFoo_eid" INT8 NOT NULL,
"CoosOfFoo_vid" INT8 NOT NULL,
"FoosOfCoo_eid" INT8 NOT NULL,
"FoosOfCoo_vid" INT8 NOT NULL
) WITH OIDS;
ALTER TABLE "FooHolding" OWNER TO tutorial;
ALTER TABLE "FooHolding" ADD CONSTRAINT "CoosOfFoo_fki" FOREIGN KEY
("CoosOfFoo_eid", "CoosOfFoo_vid")
REFERENCES "EntityVersion" ("entityID", "versionID") MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "FooHolding" ADD CONSTRAINT "FoosOfCoo_fki" FOREIGN KEY
("FoosOfCoo_eid", "FoosOfCoo_vid")
REFERENCES "EntityVersion" ("entityID", "versionID") MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE "FooHolding" ADD CONSTRAINT "FooHolding_pkey" PRIMARY KEY
("CoosOfFoo_eid", "CoosOfFoo_vid", "FoosOfCoo_eid", "FoosOfCoo_vid");
The "ON UPDATE CASCADE" is not used for the use-case described below :-)
Example records _before_ copy:
CoosOfFoo_eid | CoosOfFoo_vid | FoosOfCoo_eid | FoosOfCoo_vid
--------------+---------------+---------------+--------------
1 101 2 102
1 101 3 103
1 101 4 104
5 105 4 104
5 105 6 106
5 105 8 108
Example records _after_ copy all with CoosOfFoo_vid = 101:
CoosOfFoo_eid | CoosOfFoo_vid | FoosOfCoo_eid | FoosOfCoo_vid
--------------+---------------+---------------+--------------
1 101 2 102
1 101 3 103
1 101 4 104
5 105 4 104
5 105 6 106
5 105 8 108
1 201 2 102
1 201 3 103
1 201 4 104
(3 records have been inserted with CoosOfFoo_vid = 201)
My idea was to create a function in PL/Python with incoming parameters:
original-value, copy-value. This function is called by my client via
pyPgSQL during a bigger transaction.
Function would make something like:
rows = plpy.execute("""SELECT "FooHolding" WHERE "CoosOfFoo_vid" =
ORIGINALVALUE;""")
for row in rows:
for col in row:
plpy.execute("""INSERT INTO "FooHolding" ("CoosOfFoo_eid",
"CoosOfFoo_vid", "FoosOfCoo_eid", "FoosOfCoo_vid") VALUES (col[0],
COPYVALUE, col[2], col[3]);""")
Is this okay? Stupid or slow? I've thought it would be much better than
retrieving all records to the client, making there the modified copy and
then sent the stuff back to the database.
Actually I would need this in multiple tables, but I could generate the
functions, thus it is not a problem to have such a function for each of
those tables.
Thank you.
Anastasios
PS: I use PostgreSQL 8.1.3 on Windows XP development machine.
