Quantcast

using a generated series in function

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

using a generated series in function

John Fabiani
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

David Johnston
On Dec 15, 2011, at 23:16, John Fabiani <[hidden email]> 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.


--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

Misa Simic
In reply to this post by John Fabiani
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: [hidden email]
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

John Fabiani
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: [hidden email]
> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

John Fabiani
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: [hidden email]
> > 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

Misa Simic
In reply to this post by John Fabiani
Hi John,

Well, maybe the best would be to say on english what you want to achieve...

From SQL code in your mail - it is not clear ( at least to me...)

but: SELECT now() - it will just execute function ant there is not possible to say WHERE in that...

and like you said:

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

it works - there is no place for WHERE...

If the query have WHERE - then it also at leasy must have FROM clausule...

Kind Regards,

Misa



2011/12/16 John Fabiani <[hidden email]>
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: [hidden email]
> 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

Misa Simic
In reply to this post by John Fabiani
That is good - that you solved it... 

Well - in from it does not need to be just from table...

it needs to be some set of rows... is it Table or function (SELECT i FROM generate_series(0, 84, 7) i - is actually from function...) or from View...

SELECT * FROM (SELECT * FROM Table1) as InlineView

Also works becouse of subquery also returns some set of rows...


Kind Regrads,

Misa

2011/12/16 John Fabiani <[hidden email]>
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: [hidden email]
> > 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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

Adrian Klaver-3
In reply to this post by John Fabiani
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
[hidden email]

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

John Fabiani
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

Adrian Klaver-3
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
[hidden email]

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

David Johnston
In reply to this post by John Fabiani
From is not required if you use literals or function results (with literal input parameters).  If you reference something that 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 literal being 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 <[hidden email]> 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 ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: using a generated series in function

Bèrto ëd Sèra
Hi

From is not required if you use literals or function results (with literal input parameters). 
 
If you came from Oracle you could simply think that the "FROM DUAL" bit can be omitted in Postgresql. There may be small differences, but that is the basic idea.

Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

Loading...