lead and lag problem

[coolminded]

hi all,
i'm using postgresql 8.0.0 and i want to use the lead and lag window functions but it says "function doesn't exist when i run the following command
select lag(selling_rat e,1,'0') from currency

and i again run the following command
select selling_rate, lead(selling_ra te) over ( order by selling_rate asc) from currency
then it throws the error "syntax error at or near "over" at character 41"

is there any other way to use lead or lag.
TIA

[rski]

Widow functions appear in 8.4. You can't use it in 8.0

[coolminded]

Widow functions appear in 8.4. You can't use it in 8.0

thank you for the reply.

can you please suggest me what shall i do in 8.0 to use such functions. i mean any other method to do so???

[rski]

You will need some plpgsql function or postgres 8.4.
Put here the table structure and what do you want to achive.

[coolminded]

i have a table currency with fields
id serial,
currency_id varchar(30),
selling_rate numeric(12,2),
updated_date timestamp.

what i need is a report to show the details of the currency_id as
currency_id prev selling_rate curr. selling_rate
update_date

i ihave data in my table as

id currency_id selling_rate updated_date
1 1 20.00 2010-03-20 15:30:46
2 1 22.00 2010-03-25 11:20:22
3 1 21.22 2010-03-28 09:11:20

what i want to show is like this:

currency_id prev selling_rate curr. selling_rate update_date
1 0.00 20.00 2010-03-20 15:30:46
1 20.00 22.00 2010-03-25 11:20:22
1 22.00 21.22 2010-03-28 09:11:20


i think u understand what i mean to show in my report..

TIA

[rski]

What about that?

select 
         t1.currency_id,
         case when t2.selling_rate is null then 0 else t2.selling_rate end as previous,
         t1.selling_rate,
         t1.update_date 
from 
      test t1 left join (select * from test) t2 on 
             (t2.id=(select max(id) from test where id<(t1.id)) and               
              t1.currency_id=t2.currency_id) 
order by t1.id ;