pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
create 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