Received: from bilbo.in.mat.cc (bilbo.in.mat.cc [212.43.217.121]) by postgresql.org (Postfix) with ESMTP id 58DF34758DC for ; Thu, 28 Mar 2002 01:58:39 -0500 (EST) Received: from bilbo.in.org (localhost [127.0.0.1]) by bilbo.in.mat.cc (Postfix) with ESMTP id EBA5071183 for ; Thu, 28 Mar 2002 07:58:37 +0100 (CET) Received: from sauron (sauron.in.mat.cc [212.43.217.122]) by bilbo.in.mat.cc (Postfix) with ESMTP id 29C5571183 for ; Thu, 28 Mar 2002 07:58:35 +0100 (CET) Date: Thu, 28 Mar 2002 07:58:34 +0100 From: Mathieu Arnold To: pgsql-sql@postgresql.org Subject: create view problem Message-ID: <4876156.1017302314@sauron> X-Mailer: Mulberry/2.2.0b3 (Win32) X-wazaaa: True, true X-GnuPG-keyfile: http://mat.cc/pgp/1E041B04.txt X-GnuPG-key: 0x1E041B04 X-GnuPG-key-Fingerprint: D7 46 95 99 3A EE B6 5D BF E8 A6 48 AD 66 61 C4 1E 04 1B 04 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Archive-Number: 200203/372 X-Sequence-Number: 7246 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