pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

F_TABLE_NUMBER_RANGE equivalent in Postgresql
3+ messages / 3 participants
[nested] [flat]

* F_TABLE_NUMBER_RANGE equivalent in Postgresql
@ 2011-08-16 04:53  Yuan HOng <hongyuan1306@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Yuan HOng @ 2011-08-16 04:53 UTC (permalink / raw)
  To: pgsql-sql

Hi,

With MS-SQL sever, there is a built-in utility function
dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the
given range. For example:

select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return:

number
-----------
1
2
3
4
5

This is extremely convenient for generating consecutive dates. For
example, I want to find out the daily sales information and if on some
day there is no sales, the query should return 0 instead of a missing
date.

In this case it is necessary to first create a list of consecutive
dates and then left join to some fact table.

With the range function I can simply write something like

select start_date + interval number
from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days)

How does one accomplish such task with Postgresql?

-- 
Hong Yuan

大管家网上建材超市
装修装潢建材一站式购物
http://www.homemaster.cn



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: F_TABLE_NUMBER_RANGE equivalent in Postgresql
@ 2011-08-16 05:19  msi77 <msi77@yandex.ru>
  parent: Yuan HOng <hongyuan1306@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: msi77 @ 2011-08-16 05:19 UTC (permalink / raw)
  To: Yuan HOng <hongyuan1306@gmail.com>; pgsql-sql


Are you sure that dbo.F_TABLE_NUMBER_RANGE is in SQL Server?
I suppose that this is UDF written by user.


16.08.2011, 08:53, "Yuan HOng" <hongyuan1306@gmail.com>:
> Hi,
>
> With MS-SQL sever, there is a built-in utility function
> dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the
> given range. For example:
>
> select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return:
>
> number
> -----------
> 1
> 2
> 3
> 4
> 5
>
> This is extremely convenient for generating consecutive dates. For
> example, I want to find out the daily sales information and if on some
> day there is no sales, the query should return 0 instead of a missing
> date.
>
> In this case it is necessary to first create a list of consecutive
> dates and then left join to some fact table.
>
> With the range function I can simply write something like
>
> select start_date + interval number
> from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days)
>
> How does one accomplish such task with Postgresql?
>
> --
> Hong Yuan
>
> 大管家网上建材超市
> 装修装潢建材一站式购物
> http://www.homemaster.cn
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: F_TABLE_NUMBER_RANGE equivalent in Postgresql
@ 2011-08-16 05:21  Pavel Stehule <pavel.stehule@gmail.com>
  parent: msi77 <msi77@yandex.ru>
  0 siblings, 0 replies; 3+ messages in thread

From: Pavel Stehule @ 2011-08-16 05:21 UTC (permalink / raw)
  To: msi77 <msi77@yandex.ru>; +Cc: Yuan HOng <hongyuan1306@gmail.com>; pgsql-sql

Hello

this is not built in MSSQL, but PostgreSQL has a "generate_series" function

Regards

Pavel Stehule

2011/8/16 msi77 <msi77@yandex.ru>:
>
> Are you sure that dbo.F_TABLE_NUMBER_RANGE is in SQL Server?
> I suppose that this is UDF written by user.
>
>
> 16.08.2011, 08:53, "Yuan HOng" <hongyuan1306@gmail.com>:
>> Hi,
>>
>> With MS-SQL sever, there is a built-in utility function
>> dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the
>> given range. For example:
>>
>> select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return:
>>
>> number
>> -----------
>> 1
>> 2
>> 3
>> 4
>> 5
>>
>> This is extremely convenient for generating consecutive dates. For
>> example, I want to find out the daily sales information and if on some
>> day there is no sales, the query should return 0 instead of a missing
>> date.
>>
>> In this case it is necessary to first create a list of consecutive
>> dates and then left join to some fact table.
>>
>> With the range function I can simply write something like
>>
>> select start_date + interval number
>> from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days)
>>
>> How does one accomplish such task with Postgresql?
>>
>> --
>> Hong Yuan
>>
>> 大管家网上建材超市
>> 装修装潢建材一站式购物
>> http://www.homemaster.cn
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2011-08-16 05:21 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-16 04:53 F_TABLE_NUMBER_RANGE equivalent in Postgresql Yuan HOng <hongyuan1306@gmail.com>
2011-08-16 05:19 ` msi77 <msi77@yandex.ru>
2011-08-16 05:21   ` Pavel Stehule <pavel.stehule@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox