Filtering by window function result in Postgresql

Question

Ok, initially this was just a joke we had with a friend of mine, but it turned into interesting technical question :)

I have the following stuff table:

CREATE TABLE stuff (     id serial PRIMARY KEY,     volume integer NOT NULL DEFAULT 0,     priority smallint NOT NULL DEFAULT 0, ); 

The table contains the records for all of my stuff, with respective volume and priority (how much I need it).

I have a bag with specified volume, say 1000. I want to select from the table all stuff I can put into a bag, packing the most important stuff first.

This seems like the case for using window functions, so here is the query I came up with:

select s.*, sum(volume) OVER previous_rows as total  from stuff s  where total < 1000  WINDOW previous_rows as   (ORDER BY priority desc ROWS between UNBOUNDED PRECEDING and CURRENT ROW)  order by priority desc 

The problem with it, however, is that Postgres complains:

ERROR:  column "total" does not exist LINE 3:  where total < 1000 

If I remove this filter, total column gets properly calculated, results properly sorted but all stuff gets selected, which is not what I want.

So, how do I do this? How do I select only items that can fit into the bag?

Answer 1

I don't know if this qualifies as "more elegant" but it is written in a different manner than Cybernate's solution (although it is essentially the same)

 WITH window_table AS  (     SELECT s.*,            sum(volume) OVER previous_rows as total    FROM stuff s    WINDOW previous_rows as          (ORDER BY priority desc ROWS between UNBOUNDED PRECEDING and CURRENT ROW) )  SELECT *  FROM window_table WHERE total < 1000 ORDER BY priority DESC  

If by "more elegant" you mean something that avoids the sub-select, then the answer is "no"