PostgreSQL query with generated columns

Question

I have a schema as show like the below, and I want to run a query where I get a column in the output for every row of the points table.

So for each usage row I want to multiply the amount of the usage times the amount for the referenced points_id, and then sum that up and group by person. So for the example data I'd want output that looked like this:

 Name  | foo  | bar  | baz  
-------|------|------|------
 Scott | 10.0 | 24.0 | 0.0  
 Sam   | 0.0  | 0.0  | 46.2

Here's the schema/data:

CREATE TABLE points (
   ident int primary key NOT NULL,
   abbrev VARCHAR NOT NULL,
   amount real NOT NULL
);

CREATE TABLE usage (
  ident int primary key NOT NULL,
  name VARCHAR NOT NULL,
  points_id integer references points (ident),
  amount real
);

INSERT INTO points (ident, abbrev, amount) VALUES
  (1, 'foo', 1.0),
  (2, 'bar', 2.0),
  (3, 'baz', 3.0);

INSERT INTO usage (ident, name, points_id, amount) VALUES 
  (1, 'Scott', 1, 10),
  (2, 'Scott', 2, 12),
  (3, 'Sam', 3, 3.4),
  (4, 'Sam', 3, 12);

I'm using PostgreSQL 9.2.8

The data is just sample. There are thousands of rows in the real usage table and probably a dozen in the points table. The real intent here is I don't want to hardcode all the points summations as I use them in many functions.

Abhishek · Accepted Answer · 2015-11-05 19:06:05Z

0

select 
t1.name,
sum(case when t2.abbrev='foo' then t1.amount*t2.amount else 0 end) as foo,
sum(case when t2.abbrev='bar' then t1.amount*t2.amount else 0 end) as bar,
sum(case when t2.abbrev='baz' then t1.amount*t2.amount else 0 end) as baz
from usage t1 inner join points t2 on t1.points_id=t2.ident
group by t1.name;

SQL Fiddle Example:http://sqlfiddle.com/#!15/cc84a/6;

Use following PostgreSQL function for dynamic cases:

create or replace function sp_test()
returns void as
$$

declare cases character varying;
declare sql_statement text;
begin

select string_agg(concat('sum(case when t2.abbrev=','''',abbrev,'''',' then t1.amount*t2.amount else 0 end) as ', abbrev),',') into cases from points;

drop table if exists temp_data;

sql_statement=concat('create temporary table temp_data as select 
t1.name,',cases ,' 
from usage t1 inner join points t2 on t1.points_id=t2.ident
group by t1.name ');

execute sql_statement;

end;
$$
language 'plpgsql';

Function uses temporary table to store dynamic columns data.

Call function in following way to get data:

select * from sp_test(); select * from temp_data;

edited Nov 5, 2015 at 19:06

answered Nov 5, 2015 at 14:58

Abhishek

4,8094 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gargoyle Over a year ago

That's specific to the data shown. I was just giving some example data. There are thousands of 'usage' rows and over a dozen 'points' rows. Needs to be dynamic.

Collectives™ on Stack Overflow

PostgreSQL query with generated columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related