Thread: using a generated series in function

Hi,

I am attempting (without success) use the generated series of dates that come 
from:
select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as 
week_date from generate_series(0,84,7) i 

in a function.
select function_name(integer, date);  -- function returns a numeric

This does NOT work:
select (function_name(303, week_date::date)) as week_date where week_date in 
(select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as 
week_date from generate_series(0,84,7) i )

The error is:
ERROR:  column "week_date" does not exist
LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...

I hope I can do this?  What am I doing wrong?
Johnf

On Dec 15, 2011, at 23:16, John Fabiani <johnf@jfcomputer.com> wrote:

> Hi,
>
> I am attempting (without success) use the generated series of dates that come
> from:
> select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i
>
> in a function.
> select function_name(integer, date);  -- function returns a numeric
>
> This does NOT work:
> select (function_name(303, week_date::date)) as week_date where week_date in
> (select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i )
>
> The error is:
> ERROR:  column "week_date" does not exist
> LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
>
> I hope I can do this?  What am I doing wrong?
> Johnf
>
>

"select ... as week_date where week_date in ..."

You are missing a FROM clause for the main select; the only FROM you have is part of the IN target subquery.

David J.

It is not totally clear to me what are u trying to do... But in second
query it seems there is missing "from"

It is as

SELECT week-date::date AS week-date WHERE week-date in (subquery which
have from)

So week-date column in main query does not exist..

Sent from my Windows Phone From: John Fabiani
Sent: 16 December 2011 05:16
To: pgsql-sql@postgresql.org
Subject: [SQL] using a generated series in function
Hi,

I am attempting (without success) use the generated series of dates that come
from:
select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i

in a function.
select function_name(integer, date);  -- function returns a numeric

This does NOT work:
select (function_name(303, week_date::date)) as week_date where week_date in
(select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i )

The error is:
ERROR:  column "week_date" does not exist
LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...

I hope I can do this?  What am I doing wrong?
Johnf




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Actually what would the "from" be - this could be a newbie issue here?  
Neither statement requires a "from" because neither of the statements uses a 
table - I think!  I'll try to add one but the first part is a function like a 
any other function.  What is the "from" when you do:
"select now()"  - really I don't know!

The second part is tricky because I don't really understand it.  Howerver, I 
have used it several times (got it off the web somewhere) but only in a "for 
loop".  If I just run it by it's self it generates a table of dates.  
Therefore, I have always thought of it as a function.  Again, like "select 
now()"

So I know this must sound like I'm sort of idiot - just never considered the 
second half (the part that provides the dates) anything other than a postgres 
function.

Johnf

On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
> It is not totally clear to me what are u trying to do... But in second
> query it seems there is missing "from"
> 
> It is as
> 
> SELECT week-date::date AS week-date WHERE week-date in (subquery which
> have from)
> 
> So week-date column in main query does not exist..
> 
> Sent from my Windows Phone From: John Fabiani
> Sent: 16 December 2011 05:16
> To: pgsql-sql@postgresql.org
> Subject: [SQL] using a generated series in function
> Hi,
> 
> I am attempting (without success) use the generated series of dates that
> come from:
> select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i
> 
> in a function.
> select function_name(integer, date);  -- function returns a numeric
> 
> This does NOT work:
> select (function_name(303, week_date::date)) as week_date where week_date in
> (select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
> week_date from generate_series(0,84,7) i )
> 
> The error is:
> ERROR:  column "week_date" does not exist
> LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
> 
> I hope I can do this?  What am I doing wrong?
> Johnf

I have solved my problem.  But this still does not explain the idea of 
"from"????

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
On Friday, December 16, 2011 02:46:18 AM John Fabiani wrote:
> Actually what would the "from" be - this could be a newbie issue here?
> Neither statement requires a "from" because neither of the statements uses a
> table - I think!  I'll try to add one but the first part is a function like
> a any other function.  What is the "from" when you do:
> "select now()"  - really I don't know!
> 
> The second part is tricky because I don't really understand it.  Howerver, I
> have used it several times (got it off the web somewhere) but only in a
> "for loop".  If I just run it by it's self it generates a table of dates.
> Therefore, I have always thought of it as a function.  Again, like "select
> now()"
> 
> So I know this must sound like I'm sort of idiot - just never considered the
> second half (the part that provides the dates) anything other than a
> postgres function.
> 
> Johnf
> 
> On Friday, December 16, 2011 01:30:53 AM Misa Simic wrote:
> > It is not totally clear to me what are u trying to do... But in second
> > query it seems there is missing "from"
> > 
> > It is as
> > 
> > SELECT week-date::date AS week-date WHERE week-date in (subquery which
> > have from)
> > 
> > So week-date column in main query does not exist..
> > 
> > Sent from my Windows Phone From: John Fabiani
> > Sent: 16 December 2011 05:16
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] using a generated series in function
> > Hi,
> > 
> > I am attempting (without success) use the generated series of dates that
> > come from:
> > select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6))
> > as
> > week_date from generate_series(0,84,7) i
> > 
> > in a function.
> > select function_name(integer, date);  -- function returns a numeric
> > 
> > This does NOT work:
> > select (function_name(303, week_date::date)) as week_date where
> > week_date in (select (cast(date_trunc('week', '2011-11-20'::date ) as
> > date) + (i+6)) as week_date from generate_series(0,84,7) i )
> > 
> > The error is:
> > ERROR:  column "week_date" does not exist
> > LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...
> > 
> > I hope I can do this?  What am I doing wrong?
> > Johnf

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

It is not totally clear to me what are u trying to do... But in second
query it seems there is missing "from"

It is as

SELECT week-date::date AS week-date WHERE week-date in (subquery which
have from)

So week-date column in main query does not exist..

Sent from my Windows Phone From: John Fabiani
Sent: 16 December 2011 05:16
To: pgsql-sql@postgresql.org
Subject: [SQL] using a generated series in function
Hi,

I am attempting (without success) use the generated series of dates that come
from:
select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i

in a function.
select function_name(integer, date);  -- function returns a numeric

This does NOT work:
select (function_name(303, week_date::date)) as week_date where week_date in
(select (cast(date_trunc('week', '2011-11-20'::date ) as date) + (i+6)) as
week_date from generate_series(0,84,7) i )

The error is:
ERROR:  column "week_date" does not exist
LINE 1: select (xchromasun._chromasun_getqtyordered(303, week_date::...

I hope I can do this?  What am I doing wrong?
Johnf




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

As always I respect your insights - Adrian.  I do understand what I did wrong 
in my first attempt at getting my statement to work.  But it is either over my 
head or there is something missing.  Where is the "from" in
select now()?

I have been using similar SQL statements for years.  I never questioned why 
there was not a 'from' until this list noted that I was missing a 'From'.   I 
then went to the postgres site to read.  That's how I determined what I had 
done incorrectly.

I hope this is not one of those things like javascript where all white space 
is ignored unless it's not!  I hate that language!  It appears that everything 
needs a 'From' in SQL (reading the doc's) and the above statement is missing a 
'From'!

As always everyone - thanks for your help!

Johnf


On Friday, December 16, 2011 07:31:40 AM Adrian Klaver wrote:
> 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!

On Saturday, December 17, 2011 8:50:52 am John Fabiani wrote:
> As always I respect your insights - Adrian.  I do understand what I did
> wrong in my first attempt at getting my statement to work.  But it is
> either over my head or there is something missing.  Where is the "from" in
> select now()?

If it makes you happier:)

test(5432)aklaver=>SELECT * from now();             now              
-------------------------------2011-12-17 10:00:34.929144-08
(1 row)



> 
> I have been using similar SQL statements for years.  I never questioned why
> there was not a 'from' until this list noted that I was missing a 'From'.  
> I then went to the postgres site to read.  That's how I determined what I
> had done incorrectly.

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


So items in the SELECT are not required to be derived from as FROM clause


> 
> I hope this is not one of those things like javascript where all white
> space is ignored unless it's not!  I hate that language!  It appears that
> everything needs a 'From' in SQL (reading the doc's) and the above
> statement is missing a 'From'!

It is a Postgres extension to the SQL standard:

http://www.postgresql.org/docs/9.0/interactive/sql-select.html#SQL-SELECT-LIST
Compatibility
"
Omitted FROM Clauses

PostgreSQL allows one to omit the FROM clause. It has a straightforward use to 
compute the results of simple expressions:

SELECT 2+2;
?column?
----------       4

Some other SQL databases cannot do this except by introducing a dummy one-row 
table from which to do the SELECT.

Note that if a FROM clause is not specified, the query cannot reference any 
database tables. For example, the following query is invalid:

SELECT distributors.* WHERE distributors.name = 'Westward';

PostgreSQL releases prior to 8.1 would accept queries of this form, and add an 
implicit entry to the query's FROM clause for each table referenced by the 
query. This is no longer allowed.
"

> 
> As always everyone - thanks for your help!
> 


-- 
Adrian Klaver
adrian.klaver@gmail.com

From is not required if you use literals or function results (with literal input parameters).  If you reference
somethingthat is not one of these it has to come from somewhere and that location is the from/join part of the query. 

In your query foo.work_date is not a literal or function and so must be sourced from somewhere.  2011-11-20 is a
literalbeing fed into a function and so does not require a From clause in order to be evaluated. 

David J.

On Dec 17, 2011, at 11:50, John Fabiani <johnf@jfcomputer.com> wrote:

> As always I respect your insights - Adrian.  I do understand what I did wrong
> in my first attempt at getting my statement to work.  But it is either over my
> head or there is something missing.  Where is the "from" in
> select now()?
>
> I have been using similar SQL statements for years.  I never questioned why
> there was not a 'from' until this list noted that I was missing a 'From'.   I
> then went to the postgres site to read.  That's how I determined what I had
> done incorrectly.
>
> I hope this is not one of those things like javascript where all white space
> is ignored unless it's not!  I hate that language!  It appears that everything
> needs a 'From' in SQL (reading the doc's) and the above statement is missing a
> 'From'!
>
> As always everyone - thanks for your help!
>
> Johnf
>
>
> On Friday, December 16, 2011 07:31:40 AM Adrian Klaver wrote:
>> 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!
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql