Обсуждение: pgplsql - accessing rows from below or above.
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?
Issue:
column a
a
b
c
d
e
f
g
g
h
i
j
I am trying to get the column row/value a to compare against all other values, then move onto b and compare all other values - so again, reaching outside of its row.
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;
$$