Re: [SQL] using a generated series in function
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a generated series in function
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
Re: [SQL] using a generated series in function
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a generated series in function
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
> 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
>
Re: [SQL] using a generated series in function
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
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Re: [SQL] 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.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Column "..." does not exist (view + union)
Assuming the following simple setup with two data tables, one mapping table, and one view - -- CREATE TABLE dossier ( id SERIAL NOT NULL PRIMARY KEY ); CREATE TABLE contact ( id SERIAL NOT NULL PRIMARY KEY, nameTEXT NOT NULL, firstname TEXT NULL ); CREATE TABLE dossier_contact ( dossier_id INTEGER NOT NULL REFERENCES dossier(id), contact_id INTEGER NOT NULL REFERENCES contact(id), ctype INTEGER NOT NULL, PRIMARY KEY (dossier_id, contact_id) ); CREATE VIEW dossier_contact_v AS SELECT dc.dossier_id, dc.contact_id, dc.ctype, (CASE WHEN c.firstname IS NOT NULL THEN c.name || ', ' || c.firstname ELSE c.name END) AS name FROM dossier_contact dc JOIN contact c ON c.id = dc.contact_id; -- - running this query - SELECT name FROM dossier_contact_v WHERE dossier_id = 56993 AND ctype = 234 UNION SELECT name FROM dossier_contact_v WHERE dossier_id = -1 AND ctype = -1 ORDER BY ctype; - fails with the following error message: ERROR: column "ctype" does not exist LINE 10: ORDER BY ctype; ^ The same query works fine without the ORDER BY, without the UNION, or when I select the "ctype" column in addition to "name". Why? Using an alias in the FROM clause gives a different error: SELECT x.name FROM dossier_contact_v x WHERE x.dossier_id = 56993 AND x.ctype = 234 UNION SELECT x.name FROM dossier_contact_v x WHERE x.dossier_id = -1 AND x.ctype = -1 ORDER BY x.ctype; ERROR: missing FROM-clause entry for table "x" LINE 10: ORDER BY x.ctype ^ I am using "PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit". thanks, stefan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
