Re: pgplsql - accessing rows from below or above.

On Thu, Oct 13, 2011 at 5:57 PM, Henry Drexler <alonup8tb(at)gmail(dot)com> wrote:

>
> On Thu, Oct 13, 2011 at 5:10 PM, Henry Drexler <alonup8tb(at)gmail(dot)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;
$$