Issue:
------------------------------
I am trying to get a plpgsql to access a value above (or below) the current row itself.
Example:
------------------------------
select
node
from
(values('once'),('twice'))
hello(node);
This produces
once
twice
doing this query:
select
node,
lag(node,1) over (rows between unbounded preceding and unbounded following)
from
(values('once'),('twice'))
hello(node);
produces:
once
twice once
That is the effect I am trying to get in pgplsql.
Example of attempt at a solution:
------------------------------
I have no problem writing pgplsql to affect data on same rows, but I don't seem to comprehend or see in the documentation as far as I can tell you to do this.
(as an aside, why I want this I so I can do comparisons of say 80 rows vs the current row and evaluate to something. I have this working in excel vba but am trying to put it into the db due to volume of data etc...)
I thought maybe I could do a query in pgplsql and move its value by the number of rows - but this seems quote verbose and the query never seems to end.
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'))
hello(node)
)
;
u := 1;
while u <= total_count loop
t := (
select
upon.node--,
--row_number
from
(select
lag(hello.node,1) over (rows between unbounded preceding and unbounded following) as node
from
(values('once'),('twice'))
hello(node)
limit 1 offset u
) as upon
)
;
end loop;
return t;
end;
$$
and the query:
select
node,
valueabovereal(node)
from
(values('once'),('twice'))
hello(node);
Does anyone have any pointers or tutorials that show how to access outside of your current row in pgplsql?