Thread: auto-increment in a view

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

see code below, this is postgresql 8.3.7

--drop table test;create table test(id integer primary key, "value" integer);
insert into test (id, "value") values (generate_series(1,1000000),
generate_series(1,1000000)/4);
vacuum analyze test;
--drop view testview;
create or replace view testview as (select value from test group by value);
select * from testview limit 5;
--2734 ms (warm)

create or replace view testview2 as (select null::serial, value from
test group by value);
--ERROR:  type "serial" does not exist
create or replace view testview2 as (create sequence tempseq;select
nextval('tempseq'), value from test group by value;create sequence
tempseq;);
--ERROR:  syntax error at or near "create"

create type testview2_type as (recnr integer, "value" integer);
create or replace function testview2() returns setof testview2_type as $$
declare
  t_recnr integer:=0;
  t_rec record;
  t_rec2 testview2_type;
begin
for t_rec in select value from test group by value
loop
  t_recnr:=t_recnr+1;
  t_rec2.recnr:=t_recnr;
  t_rec2."value":=t_rec."value";
  return next t_rec2;
end loop;
return;
end
$$ language plpgsql;

create or replace view testview2 as select * from testview2();
select * from testview2 limit 5;
--3946 ms (warm)

--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw

On 03/09/2009 17:22, 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.

Just a wild notion, but would the generate_series function be any use to
you?

  http://www.postgresql.org/docs/8.4/static/functions-srf.html

HTH,

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

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;

> Just a wild notion, but would the generate_series function be any use to
> you?

Good idea, but i can't get it to work.

create or replace view testview3 as (
select generate_series(1,(select count(*) from test group by "value")), value
from test group by value);
select * from testview3 limit 5;
--ERROR:  more than one row returned by a subquery used as an expression

When i put the generate_series in the FROM clause, the results will be
matched "everything to everything".

I guess the windowing function is the way to go.

Thx,

WBL


--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw