Обсуждение: can a function take a column name or is there another solution
I have a table with sampledate, meter1, meter2...etc
I am trying to develop a function or query
where:
select sampledate, (meter1 - (select meter1 where sampledate = sampledate + interval '1 days')) as consumption from mytable;
Any ideas how to make this work?
Mark King
I am trying to develop a function or query
where:
select sampledate, (meter1 - (select meter1 where sampledate = sampledate + interval '1 days')) as consumption from mytable;
Any ideas how to make this work?
Mark King
Re: can a function take a column name or is there another solution
От
"Oliveiros d'Azevedo Cristina"
Дата:
Hello!
I fail to understand what you are attempting to do.
What does your table represent and what output you expect to have..?
To my best knowledge this (select meter1 where sampledate = sampledate + interval '1 days') is not valid SQL so could you please explain in english the info
you want to obtain from your table?
Best,
Oliveiros
----- Original Message -----
From: Mark KingSent: Monday, April 18, 2011 4:16 PMSubject: [NOVICE] can a function take a column name or is there another solutionI have a table with sampledate, meter1, meter2...etc
I am trying to develop a function or query
where:
select sampledate, (meter1 - (select meter1 where sampledate = sampledate + interval '1 days')) as consumption from mytable;
Any ideas how to make this work?
Mark King
Mark King <fires10@gmail.com> writes: > I have a table with sampledate, meter1, meter2...etc > I am trying to develop a function or query > where: > select sampledate, (meter1 - (select meter1 where sampledate = sampledate + > interval '1 days')) as consumption from mytable; > Any ideas how to make this work? I think what you're looking for is window functions, specifically the LEAD/LAG functions. You need Postgres >= 8.4 for those though. regards, tom lane
The Lead/Lag functions are exactly what I am looking for.
Thank you
On Mon, Apr 18, 2011 at 11:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think what you're looking for is window functions, specifically theMark King <fires10@gmail.com> writes:
> I have a table with sampledate, meter1, meter2...etc
> I am trying to develop a function or query
> where:
> select sampledate, (meter1 - (select meter1 where sampledate = sampledate +
> interval '1 days')) as consumption from mytable;
> Any ideas how to make this work?
LEAD/LAG functions. You need Postgres >= 8.4 for those though.
regards, tom lane