From: John Fabiani <johnf@jfcomputer.com>
To: pgsql-sql@postgresql.org
Subject: Re: using a generated series in function
Date: Fri, 16 Dec 2011 03:56:48 -0800
Message-ID: <1424284.aaP0Qsvkam@linux-12>
User-Agent: KMail/4.7.2 (Linux/3.1.0-1.2-desktop; KDE/4.7.2; x86_64; ; )
In-Reply-To: <1479832.Z2paPkhlAQ@linux-12>
References: <-6224470694062641569@unknownmsgid> <1479832.Z2paPkhlAQ@linux-12>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"

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