Re: using a generated series in function - Mailing list pgsql-sql

From Misa Simic
Subject Re: using a generated series in function
Date
Msg-id CAH3i69mJtPkPxKMFkwx+EGmmkAp+0eyMU3t=nQJ2hALSuX6q2g@mail.gmail.com
Whole thread Raw
In response to Re: using a generated series in function  (John Fabiani <johnf@jfcomputer.com>)
List pgsql-sql
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 <johnf@jfcomputer.com>
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

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

pgsql-sql by date:

Previous
From: John Fabiani
Date:
Subject: Re: using a generated series in function
Next
From: Misa Simic
Date:
Subject: Re: using a generated series in function