Re: using a generated series in function

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"????

From original query:
..(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
and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must
write out the expression instead.
...
"

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

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

>
> 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
>
> The above works!
>
> Johnf
>

--
Adrian Klaver
adrian.klaver@gmail.com