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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
