Skip site navigation (1) Skip section navigation (2)

Strange situation with two tables.

From: Terry Yapt <yapt(at)technovell(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Strange situation with two tables.
Date: 2002-09-11 13:34:00
Message-ID: 3D7F4648.3D29B2C0@technovell.com (view raw or flat)
Thread:
Lists: 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 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:

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

*/

Responses

pgsql-novice by date

Next:From: Kevin_WalshDate: 2002-09-11 14:17:26
Subject: real beginner's questions
Previous:From: Florian LitotDate: 2002-09-11 12:34:58
Subject: backup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group