Thread: F_TABLE_NUMBER_RANGE equivalent in Postgresql
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
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
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 >