Re: auto-increment in a view

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;