Postgresql.. reorder rows based on "username" column alphabetically descendat

Question

I have a Postgresql 9.4.1 table, like this...

--------+-----------+-----------+----------------
serial  |  username |  userprod |  prodprice
--------+-----------+-----------+----------------
1       |  Zack     |  candy    |  44$
2       |  Tom      |  shoes    |  54$
3       |  Steve    |  pants    |  23$
4       |  Paul     |  hood     |  65$
5       |  John     |  cap      |  23$
6       |  Zack     |  tshirt   |  56$
7       |  Tom      |  pullover |  21$
8       |  Steve    |  socks    |  42$
9       |  Paul     |  shorts   |  23$
10      |  John     |  masc     |  21$

Rows are ordered by the serial column, how can I display the same table but, reordering all the rows based on the "username" column alphabetically descending? I would like Postgres to do it automatically on real time on a separate table. I guess I can use View? How can I do that?

Answer 1

Sure, you can use a VIEW to return ordered rows.

CREATE VIEW v_tbl_username AS
TABLE tbl ORDER BY username;

And another one for your rows sorted by the serial column:

CREATE VIEW v_tbl_serial AS
TABLE tbl ORDER BY serial;

Read @a_horse's comment addressing your basic misconception.

The order holds unless it's overruled by another ORDER BY in the outer query.

Views work with early binding: Only columns present at the time of creation are included. SELECT * FROM tbl or TABLE tbl are resolved to the list of currently existing column. If the underlying table is changed later, that's not cascaded to the view automatically.