Skip site navigation (1) Skip section navigation (2)

pgplsql - accessing rows from below or above.

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 (view raw or flat)
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?

Responses

pgsql-novice by date

Next:From: Henry DrexlerDate: 2011-10-13 21:57:32
Subject: Re: pgplsql - accessing rows from below or above.
Previous:From: Hany ABOU-GHOURYDate: 2011-10-13 20:05:44
Subject: Partitioning in PostgrSQL 9.04

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group