Postgres Pro
Downloads
Home   >   mailing lists

Re: pgplsql - accessing rows from below or above. - Mailing list pgsql-novice

From Henry Drexler
Subject Re: pgplsql - accessing rows from below or above.
Date
Msg-id CAAtgU9TbtVkiG7bbZZA0Gu50CZnEH3E=J+6jomSan4s5bKe7sQ@mail.gmail.com
Whole thread Raw
In response to Re: pgplsql - accessing rows from below or above.  (Henry Drexler <alonup8tb@gmail.com>)
List pgsql-novice
Tree view


On Thu, Oct 13, 2011 at 5:57 PM, Henry Drexler <alonup8tb@gmail.com> wrote:

On Thu, Oct 13, 2011 at 5:10 PM, Henry Drexler <alonup8tb@gmail.com> wrote:
Issue:

 
solved it.

I had to do a nested if then within the loop to get the iteration.  (also I forgot the u = u + 1; inside the loop to enable the iteration)

here is the solution.

create or replace function valueabovereal(node text) returns text language plpgsql as $$
declare 
t text;
total_count integer;
u integer;
begin
total_count := (
select
count(*)
from
(values('once'),('twice'),('three'),('four'),('five'),('six'),('threee'))
hello(node)
)
;
u := 1;
while u <= total_count loop
    
select into t
upon.node
from
(select
lag(hello.node,1) over (rows between unbounded preceding and unbounded following) as node
from
(values('once'),('twice'),('three'),('four'),('five'),('six'),('threee'))
hello(node)
limit 1 offset u
) as upon ;
 
u = u + 1;

    if t = $1 then exit;
    end if;
end loop;
return t;
end;
$$

pgsql-novice by date:

Previous
From: Devrim GÜNDÜZ
Date:
Subject: Re: Partitioning in PostgrSQL 9.04
Next
From: Marcus Engene
Date:
Subject: What could lock up pg_stat_activity