Skip to content
Advertisement

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:

This yields useful results like:

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:

and just get

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.

Advertisement

Answer

Instead of using a sequence, use row_number():

You can put this into a view.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement