I have a bit theoretical and/or practical problem that I am trying (and I would like) to resolve with PL/PGSQL language.
Sum(Table A) generates Table B.
Primary key on Table B must be saved to Table A.
A lot of different people (client connections) is working and doing entries against Table A.
CREATE TABLE debits
( sequen numeric(4,0), -- really it is a serial type
PRIMARY KEY (sequen, customer_number)
) WITHOUT OIDS;
-- Debits for customer 1
INSERT INTO debits VALUES (1, 1, 12, 1); -- already billed . Bill Number = 1
INSERT INTO debits VALUES (2, 1, -2, 1); -- already billed. Bill Number = 1
INSERT INTO debits VALUES (3, 1, 22, null);
INSERT INTO debits VALUES (4, 1, 8, null);
INSERT INTO debits VALUES (5, 1, 1, null);
INSERT INTO debits VALUES (6, 1, 2, null);
-- Debits for customer 2
INSERT INTO debits VALUES (7, 2, 8, null);
INSERT INTO debits VALUES (8, 2, 2, null);
CREATE TABLE bills
PRIMARY KEY (bill_number)
) WITHOUT OIDS;
INSERT INTO bills VALUES (1, 'John Doe', 10); -- Here are debits: 1,1 and 2,1
I need to create an AGGREGATE SELECT <SUM(debits.money)> to insert only ONE bill by all debits rows. Only a bill by customer. Ok, that's already done. After (or at the same time), I need to UPDATE debits.bill_number COLUMN to reflect the bills.bill_number on debits.bill_number.
And here is the problem because more client connections are inserting debits (and perhaps to the same customer_number I am working with), but I _ONLY_ MUST to update the debits.bill_number to that debits.ROWS I have treated in the bill.ROW creation step.
I have tried with:
But perhaps same client connection runs the procedure twice.
In this situation I have an error:
'ERROR: Relation 'xxxxxxxxxxxx' already exists'.
If I DROP temporary table previous to the CREATE TEMPORARY TABLE sentence into the pl/pgsql function,
then I obtain an error:
'ERROR: pg_aclcheck: class "pg_temp_5470_7" not found'
SELECT xx GROUP BY xx FOR UPDATE OF debits.bill_number
This does not work at all.....
I am off ideas now. I don't know how to continue without a really "row by row work and check". :-\
Of course, previous is a simple example with less tables and columns than the real situation...
Somebody have any idea how to solve this situation ?
Thanks in advance...
pgsql-novice by date
|Next:||From: Kevin_Walsh||Date: 2002-09-11 14:17:26|
|Subject: real beginner's questions|
|Previous:||From: Florian Litot||Date: 2002-09-11 12:34:58|