0

Using PostgreSQL 9.1.3 I have a points table like so (What's the right way to show tables here??)

| Column |     Type          |            Table Modifiers                          | Storage
|--------|-------------------|-----------------------------------------------------|----------|
| id     | integer           | not null default nextval('points_id_seq'::regclass) | plain    |
| name   | character varying | not null                                            | extended |
| abbrev | character varying | not null                                            | extended |
| amount | real              | not null                                            | plain    |

In another table, orders I have a bunch of columns where the name of the column exists in the points table via the abbrev column, as well as a total_points column

| Column       | Type   | Table Modifiers    |
|--------------|--------|--------------------|
| ud           | real   | not null default 0 |
| sw           | real   | not null default 0 |
| prrv         | real   | not null default 0 |
| total_points | real   | default 0          |

So in orders I have the sw column, and in points I'll now have an amount that realtes to the column where abbrev = sw

I have about 15 columns like that in the points table, and now I want to set a trigger so that when I create/update an entry in the points table, I calculate a total score. Basically with just those three shown I could do it long-hand like this:

UPDATE points
SET total_points =
    ud * (SELECT amount FROM points WHERE abbrev = 'ud') +
    sw * (SELECT amount FROM points WHERE abbrev = 'sw') +
    prrv * (SELECT amount FROM points WHERE abbrev = 'prrv')  
WHERE ....

But that's just plain ugly and repetative, and like I said there are really 15 of them (right now...). I'm hoping there's a more sophisticated way to handle this.

In general each of those silly names on the orders table represents a type of work associated with the order, and each of those types has a 'cost' to it, which is stores in the points table. I'm not married to this structure if there's a cleaner setup.

Gargoyle
  • 9,590
  • 16
  • 80
  • 145
  • If someone is going to suggest a different structure, I should note that for every order I'll want to know the value of each of those items, even if it's a 0 – Gargoyle Aug 11 '14 at 03:17
  • The best way to show the table definition is what you get with `\d tbl` in psql. Or the complete `CREATE` script (which you can copy from pgAdmin in the object browser). – Erwin Brandstetter Aug 11 '14 at 11:39
  • You don't want `UPDATE points`, you want `UPDATE orders` ... – Erwin Brandstetter Aug 11 '14 at 11:50

1 Answers1

0

"Serialize" the costs for orders:

CREATE TABLE order_cost (
   order_cost_id serial PRIMARY KEY
 , order_id      int NOT NULL REFERENCES order
 , cost_type_id  int NOT NULL REFERENCES points
 , cost          int NOT NULL DEFAULT 0  -- in Cent
);

For a single row:

UPDATE orders o
SET    total_points = COALESCE((
   SELECT sum(oc.cost * p.amount) AS order_cost
   FROM   order_cost oc
   JOIN   points p ON oc.cost_type_id = p.id
   WHERE  oc.order_id = o.order_id
   ), 0);
WHERE  o.order_id =  $<order_id>   -- your order_id here ...

Never use the lossy type real for currency data. Use exact types like money, numeric or just integer - where integer is supposed to store the amount in Cent.

More advice in this closely related example:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • So if I do this you're suggesting I remove the other columns from the orders table, right? This would simply replace that? I've been trying to make much better posts for you too :) – Gargoyle Aug 11 '14 at 15:30
  • I suggest a table `orders` (plural because order is a [reserved word](http://www.postgresql.org/docs/current/interactive/sql-keywords-appendix.html); other than that I always use single terms for tables where one row hold a single entity.) with general details for the order and another table `order_cost` with 0-n cost factors for the order. – Erwin Brandstetter Aug 11 '14 at 15:57