Temp sequence in postgres view

I have a table that I order a certain way often. That ordering is the basis of a derived column we can call rank. If I query the entire table, I can have the database calculate that rank for me:

CREATE TEMP SEQUENCE the_seq;
SELECT my_table.foo, my_table.bar, nextval('the_seq') AS rank
FROM my_table
ORDER BY my_table.bar DESC;

This yields useful results like:

foo  | bar  | rank
-------------------
0005 | 2100 | 1
0003 | 1632 | 2
0002 | 1200 | 3
0001 | 899  | 4
0004 | 500  | 5

With that result set I can determine the rank of any foo. However this requires me to query the entire table and iterate through the result set, even if I just want the rank of foo ‘0001’.

Ideally I would have a view that does the rank column for me, so I can arbitrarily say:

SELECT my_table_vw.foo, my_table_vw.bar, my_table_vw.rank
FROM my_table_vw
WHERE my_table_vw.foo = '0001'

and just get

foo  | bar  | rank
-------------------
0001 | 899  | 4

However I have no idea how to construct that in the database, since views cannot construct temp sequences in their definition. Is what I am trying to do even possible? I feel like it has to be because the alternatives, maintaining rank on insert or querying the entire table seem ludicrous.

Answer

Instead of using a sequence, use row_number():

SELECT my_table.foo, my_table.bar, row_number() over (order by my_table.bar desc) as rank
FROM my_table;

You can put this into a view.

Advertisement

Answer