Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A733FB5D803 for ; Tue, 16 Aug 2011 02:22:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 18692-08 for ; Tue, 16 Aug 2011 05:22:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 31D2DB5DBE6 for ; Tue, 16 Aug 2011 02:22:37 -0300 (ADT) Received: by vws1 with SMTP id 1so4479390vws.19 for ; Mon, 15 Aug 2011 22:22:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; bh=bYjtOwUMCuAgVIBG/MfqRHopPli0gWiM30b1Ot/Gl4c=; b=EenLjSbVpQr82eeGBu7RGD78Mi4NL8KBw2+1REBcW5sMtvJhzPv48c/zYWGOJjhNie QZEo2iZgNIo+387WL+2YrJNSlkB9LkF6joG9fLO42a9FGw5D+KkUvhACL4sAXNJ8rzRS 8fiNIBJuUMUsmQuUkH1Y8rr3Z3cuzeCaMXjoM= Received: by 10.52.74.166 with SMTP id u6mr4548847vdv.252.1313472156297; Mon, 15 Aug 2011 22:22:36 -0700 (PDT) MIME-Version: 1.0 Received: by 10.220.93.16 with HTTP; Mon, 15 Aug 2011 22:21:56 -0700 (PDT) In-Reply-To: <964111313471940@web94.yandex.ru> References: <964111313471940@web94.yandex.ru> From: Pavel Stehule Date: Tue, 16 Aug 2011 07:21:56 +0200 Message-ID: Subject: Re: F_TABLE_NUMBER_RANGE equivalent in Postgresql To: msi77 Cc: Yuan HOng , pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201108/55 X-Sequence-Number: 35723 Hello this is not built in MSSQL, but PostgreSQL has a "generate_series" function Regards Pavel Stehule 2011/8/16 msi77 : > > 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" : >> 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 >> >> =E5=A4=A7=E7=AE=A1=E5=AE=B6=E7=BD=91=E4=B8=8A=E5=BB=BA=E6=9D=90=E8=B6=85= =E5=B8=82 >> =E8=A3=85=E4=BF=AE=E8=A3=85=E6=BD=A2=E5=BB=BA=E6=9D=90=E4=B8=80=E7=AB=99= =E5=BC=8F=E8=B4=AD=E7=89=A9 >> 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 >