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