From: | Henry Drexler <alonup8tb(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | pgplsql - accessing rows from below or above. |
Date: | 2011-10-13 21:10:38 |
Message-ID: | CAAtgU9Qt12_hX+FtG-kmiaEPb9PfMs8XADCND5PQiZOqXg+Abg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Henry Drexler | 2011-10-13 21:57:32 | Re: pgplsql - accessing rows from below or above. |
Previous Message | Hany ABOU-GHOURY | 2011-10-13 20:05:44 | Partitioning in PostgrSQL 9.04 |