From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: using a generated series in function
Date: Fri, 16 Dec 2011 07:31:40 -0800
User-Agent: KMail/1.13.5 (Linux/2.6.32-36-generic; KDE/4.4.5; i686; ; )
Cc: John Fabiani <johnf@jfcomputer.com>
References: <-6224470694062641569@unknownmsgid> <1479832.Z2paPkhlAQ@linux-12>
	<1424284.aaP0Qsvkam@linux-12>
In-Reply-To: <1424284.aaP0Qsvkam@linux-12>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201112160731.41028.adrian.klaver@gmail.com>

On Friday, December 16, 2011 3:56:48 am John Fabiani wrote:
> I have solved my problem.  But this still does not explain the idea of
> "from"????

=46rom original query:
=2E.(function_name(303, week_date::date)) as week_date where week_date..

Reason why that did not work:
http://www.postgresql.org/docs/9.0/interactive/sql-select.html
SELECT List
"...
An output column's name can be used to refer to the column's value in ORDER=
 BY=20
and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you mus=
t=20
write out the expression instead.
=2E..
"

Why the below does work:
SELECT List
"The SELECT list (between the key words SELECT and FROM) specifies expressi=
ons=20
that form the output rows of the SELECT statement. The expressions can (and=
=20
usually do) refer to columns computed in the FROM clause"

=46ROM Clause
"select

    A sub-SELECT can appear in the FROM clause. This acts as though its out=
put=20
were created as a temporary table for the duration of this single SELECT=20
command. Note that the sub-SELECT must be surrounded by parentheses, and an=
=20
alias must be provided for it. A VALUES command can also be used here.
"

>=20
> select foo.week_date, xchromasun._chromasun_getqtyordered(303,
> foo.week_date) as week_qty from
>  (select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
> week_date from generate_series(0,84,7)
>  i ) as foo
>=20
> The above works!
>=20
> Johnf
>=20

=2D-=20
Adrian Klaver
adrian.klaver@gmail.com