Обсуждение: can a function take a column name or is there another solution

Поиск
Список
Период
Сортировка

can a function take a column name or is there another solution

От
Mark King
Дата:
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

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 King
Sent: Monday, April 18, 2011 4:16 PM
Subject: [NOVICE] 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

Re: can a function take a column name or is there another solution

От
Tom Lane
Дата:
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

Re: can a function take a column name or is there another solution

От
Mark King
Дата:

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:
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