Thread: create view problem
Hi I had a view : CREATE VIEWtrafic_day ASSELECT stats.ip, date(stats."time") AS date, count(*) AS nb, sum(stats.packet) AS packet, sum(stats.traffic) AS trafficFROMstatsGROUP 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 VIEWtrafic_day ASSELECT stats.ip, date(stats."time") AS date, count(*) AS nb, sum(stats.packet) AS packet, sum(stats.traffic) AS trafficFROMstatsWHERE time::date < 'now'::dateGROUP 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
> 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 VIEWtrafic_day ASSELECT stats.ip, date(stats."time") AS date, count(*) AS nb, sum(stats.packet) AS packet, sum(stats.traffic) AS trafficFROMstatsWHERE time::date < CURRENT_DATEGROUP 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...
Hi All,
At our community radio station we just upgraded from v7.0.2 to 7.1.3 and
quite a few things have changed which has caused the following questions.
1. When one does a \d on a table the type description for a varchar field
has changed from varchar(40)
tocharacter varying(40)
which is most annoying as it forces lines to wrap because of long text in
the modifier section.
Can this be set back to the former? If so how is it done?
2. Previously when issuing a \z command it listed in the 'access
permission' just the public and group permissions, eg.
annoucr | {"=r","group admin=arwR","group music_coord=r"}
Now it includes the owner name as well as all its permissions eg.
annoucr | {"=r","zedadmin=arwR","group admin=arwR","group
music_coord=r"}
Again this is annoying because of wrapping. Further it shows the
permissions of the owner for tables which we don't want people to know
about and have them try and connect to. Can this option be switched off
and hence returned to the previous format?
3. Previously the permissions on a sequence were just read-only to PUBLIC
(ie. {"=r"}) which allowed any of our applications to access the sequence
if it needed to. Now it appears that the permissions are to be allocated
to the group accessing the application and that it must be read-write.
Isn't this a security issue? Now anyone (in the group) can write to the
sequence and update it and not just the owner of the sequence. Can someone
explain for me the reason for this change? Tom?
I don't want this to sound negative as there are many good and welcome
enhancements in v7.1.3. Keep up the good work chaps.
Mind u I haven't had a look at all the new features yet so hopefully there
wont be too many more surprises. :)
--
Cheers...Gordon
-------------+------------------------------------------------------------
ADF Aircraft | Cataloguing the serial numbers of Australian Defence Force Serial | aircraft for all the
threeseries. Numbers | http://www.adf-serials.com
-------------+------------------------------------------------------------
Gordon Clarke writes:
> At our community radio station we just upgraded from v7.0.2 to 7.1.3 and
> quite a few things have changed which has caused the following questions.
>
> 1. When one does a \d on a table the type description for a varchar field
> has changed from
> varchar(40)
> to
> character varying(40)
> which is most annoying as it forces lines to wrap because of long text in
> the modifier section.
>
> Can this be set back to the former? If so how is it done?
Not without editing the source code.
> 2. Previously when issuing a \z command it listed in the 'access
> permission' just the public and group permissions, eg.
> annoucr | {"=r","group admin=arwR","group music_coord=r"}
>
> Now it includes the owner name as well as all its permissions eg.
> annoucr | {"=r","zedadmin=arwR","group admin=arwR","group
> music_coord=r"}
If the owner has some permissions they need to be stored somewhere. This
was actually part of a bug fix -- in the 7.0 case the owner actually
didn't have any permissions -- so it won't change.
> 3. Previously the permissions on a sequence were just read-only to PUBLIC
> (ie. {"=r"}) which allowed any of our applications to access the sequence
> if it needed to. Now it appears that the permissions are to be allocated
> to the group accessing the application and that it must be read-write.
The reason that you need write access is that if you call nextval you
actually write to the sequence. If you call currval you don't need write
access.
--
Peter Eisentraut peter_e@gmx.net
Hi , Any particular reson of not upgrading to 7.2.1 instead?? Peter Eisentraut wrote: > Gordon Clarke writes: > > > At our community radio station we just upgraded from v7.0.2 to 7.1.3 and > > quite a few things have changed which has caused the following questions. > > > > 1. When one does a \d on a table the type description for a varchar field > > has changed from > > varchar(40) > > to > > character varying(40) > > which is most annoying as it forces lines to wrap because of long text in > > the modifier section. > >
Hi Rajesh, > Any particular reson of not upgrading to 7.2.1 instead?? Does this version reverse the changes I mentioned below that were implemented in 7.1.3? > Peter Eisentraut wrote: > > > Gordon Clarke writes: > > > > > At our community radio station we just upgraded from v7.0.2 to 7.1.3 and > > > quite a few things have changed which has caused the following questions. > > > > > > 1. When one does a \d on a table the type description for a varchar field > > > has changed from > > > varchar(40) > > > to > > > character varying(40) > > > which is most annoying as it forces lines to wrap because of long text in > > > the modifier section. > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- --- Cheers...Gordon -------------+------------------------------------------------------------ ADF Aircraft | Cataloguing the serial numbers of Australian Defence Force Serial | aircraft for all the threeseries. Numbers | http://www.adf-serials.com -------------+------------------------------------------------------------
Hi Rajesh, > Any particular reson of not upgrading to 7.2.1 instead?? Does this version reverse the changes I mentioned below that were implemented in 7.1.3? > Peter Eisentraut wrote: > > > Gordon Clarke writes: > > > > > At our community radio station we just upgraded from v7.0.2 to 7.1.3 and > > > quite a few things have changed which has caused the following questions. > > > > > > 1. When one does a \d on a table the type description for a varchar field > > > has changed from > > > varchar(40) > > > to > > > character varying(40) > > > which is most annoying as it forces lines to wrap because of long text in > > > the modifier section. > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- --- Cheers...Gordon -------------+------------------------------------------------------------ ADF Aircraft | Cataloguing the serial numbers of Australian Defence Force Serial | aircraft for all the threeseries. Numbers | http://www.adf-serials.com -------------+------------------------------------------------------------