Re: Tabulate data incrementally

Thanks Tom and Richard for the tip on date_trunc. Is it possible in an
sql select statement to create an iterator?

For instance

select myItFunc(1,10);

would give 1,2,3,4,5,6,7,8,9,10

I'm a bit embarrassed that I don't know how to do this. My
understanding of sql functions is that not being object oriented, they
don't store state.

The reason I'm asking is that if I wanted to to use date_trunc, I think
I would need some sort of iterator to get multiple rows in one statement.

What I'm looking for is:

Employee Week Amount
John 1/1 100
Mary 1/1 0
Edward 1/2 100
etc

I'd also like to return zero or null values when the data doesn't
exist. Wouldn't I need an iterator to do that?

Thanks,

Omar

Tom Lane wrote:
> Omar Eljumaily <omar2(at)omnicode(dot)com> writes:
>
>> I want to tabulate time data on a weekly basis, but my data is entered
>> on a daily basis.
>>
>
> Something involving GROUP BY date_trunc('week', _date) might work for
> you, if your definition of week boundaries matches date_trunc's.
> If not, you could probably make a custom function that breaks at the
> boundaries you want.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>