pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate view problem
2+ messages / 2 participants
[nested] [flat]
* create view problem
@ 2002-03-28 06:58 Mathieu Arnold <mat@mat.cc>
2002-03-28 07:04 ` Re: create view problem Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 2+ messages in thread
From: Mathieu Arnold @ 2002-03-28 06:58 UTC (permalink / raw)
To: pgsql-sql
Hi
I had a view :
CREATE VIEW
trafic_day
AS
SELECT
stats.ip,
date(stats."time") AS date,
count(*) AS nb,
sum(stats.packet) AS packet,
sum(stats.traffic) AS traffic
FROM
stats
GROUP BY
stats.ip,
date(stats."time")
ORDER BY
sum(stats.traffic) DESC;
I wanted to add some test on time, so I did a
CREATE VIEW
trafic_day
AS
SELECT
stats.ip,
date(stats."time") AS date,
count(*) AS nb,
sum(stats.packet) AS packet,
sum(stats.traffic) AS traffic
FROM
stats
WHERE
time::date < 'now'::date
GROUP BY
stats.ip,
date(stats."time")
ORDER BY
sum(stats.traffic) DESC;
which
show up as :
=> select definition from pg_views where viewname = 'trafic_day';
SELECT stats.ip, date(stats."time") AS date, count(*) AS nb,
sum(stats.packet) AS packet, sum(stats.traffic) AS traffic FROM stats WHERE
(date(stats."time") < '2002-03-28'::date) GROUP BY stats.ip,
date(stats."time") ORDER BY sum(stats.traffic) DESC;
the 'now' is gone and I have the value of it instead of what I wanted...
--
Mathieu Arnold
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: create view problem
2002-03-28 06:58 create view problem Mathieu Arnold <mat@mat.cc>
@ 2002-03-28 07:04 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 2+ messages in thread
From: Christopher Kings-Lynne @ 2002-03-28 07:04 UTC (permalink / raw)
To: Mathieu Arnold <mat@mat.cc>; pgsql-sql
> I wanted to add some test on time, so I did a
> CREATE VIEW
> trafic_day
> AS
> SELECT
> stats.ip,
> date(stats."time") AS date,
> count(*) AS nb,
> sum(stats.packet) AS packet,
> sum(stats.traffic) AS traffic
> FROM
> stats
> WHERE
> time::date < 'now'::date
> GROUP BY
> stats.ip,
> date(stats."time")
> ORDER BY
> sum(stats.traffic) DESC;
'Now' is evaluated at creation time. A special hack exists if it is set as
a default column value to evaluate it at insert time. Change it to this:
CREATE VIEW
trafic_day
AS
SELECT
stats.ip,
date(stats."time") AS date,
count(*) AS nb,
sum(stats.packet) AS packet,
sum(stats.traffic) AS traffic
FROM
stats
WHERE
time::date < CURRENT_DATE
GROUP BY
stats.ip,
date(stats."time")
ORDER BY
sum(stats.traffic) DESC;
You don't even need the 'time::date' bit - just use 'time'.
Cheers,
Chris
ps. You spelled 'traffic' as 'trafic' above...
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-03-28 07:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-28 06:58 create view problem Mathieu Arnold <mat@mat.cc>
2002-03-28 07:04 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox