Strange situation with two tables.
От | Terry Yapt |
---|---|
Тема | Strange situation with two tables. |
Дата | |
Msg-id | 3D7F4648.3D29B2C0@technovell.com обсуждение исходный текст |
Ответы |
Re: Strange situation with two tables.
(Oliver Elphick <olly@lfix.co.uk>)
|
Список | pgsql-novice |
/* I have a bit theoretical and/or practical problem that I am trying (and I would like) to resolve with PL/PGSQL language. In summary: =========== 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 customer_number numeric(4,0), money numeric(4,2), bill_number numeric(4,0), 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 (bill_number numeric(4,0), customer_name varchar(40), money numeric(6,2), 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 thebills.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 amworking with), but I _ONLY_ MUST to update the debits.bill_number to that debits.ROWS I have treated in the bill.ROW creationstep. I have tried with: TEMPORARY TABLES. 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 по дате отправления: