Willy-Bas Loos wrote:
Hi, I'm trying to figure out to generate a auto-increment column in a view. There is no physical column to base it on, the view contains a group by clause, which renders that impossible. In a normal query i can create a sequence for that purpouse and drop it afterwards, but apart form it being ugly, it's impossible in a view. Another possibility is to crate a function and call that function from the view. It works, but the function is not transparent, like the view is. Meaning: the function will execute the whole query, gather the results, and when i only need a subset, it will just forget about the surplus. Isnt't there a decent way to add an incrementing value to a view? Cheers, WBL
Sounds like you need "ROWNUM" which is easy to do with windowing functions in 8.4, but on 8.3 you'll need a hack like this:
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ Then you could define your view as: create or replace view testview as select rownum(), value from test group by value; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
