Re: [HACKERS] create table and default 'now' problem ?

Thank you Tom for explanation. It's not very bothered me as far as I have
many workarounds suggested in mailing list. But I wondering because
'now'::text works as expected when I create view

create view www_auth as select a.account as user_name, a.password, b.nick as
group_name
from users a, resources b, privilege_user_map c
where a.auth_id = c.auth_id and b.res_id = c.res_id and
(a.account_valid_until is null or
a.account_valid_until > datetime('now'::text)) and
c.perm_id = 1;

Regards,
Oleg

On Tue, 21 Sep 1999, Tom Lane wrote:

> Date: Tue, 21 Sep 1999 09:40:40 -0400
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
> Cc: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>,
> pgsql-hackers(at)postgreSQL(dot)org
> Subject: Re: [HACKERS] create table and default 'now' problem ?
>
> >>>>>> how I could create table with datetime field default
> >>>>>> to 'now'::text in a way Jan did in his shoes rule example ?
>
> A couple of comments on this thread:
>
> 1. Seems to me that the easy, reliable way is just to use the
> now() function --- you don't have to make one, it's built in:
>
> create table test ( a datetime default now(), b int);
>
> This avoids all the issues about when constants get coerced, and
> probably ought to be what we recommend to newbies. However,
> this is certainly a workaround for an existing bug.
>
> 2. I believe that most of the problem with premature constant coercion
> in default values is coming from the bizarre way that default values get
> entered into the database. StoreAttrDefault essentially converts the
> parsed default-value tree back to text, constructs a SELECT statement
> using the text, parses that, and examines the resulting parsetree.
> Yech. If it were done carefully it might work, but it's not; the
> reverse parser does not do quoting carefully, does not do type coercion
> carefully, and fails to handle large parts of the expression syntax at
> all. (I've ranted about this before ... check the pghackers archives.)
>
> I have a to-do list item to rip all that code out and do it over again
> right. Might or might not get to it for 6.6 --- does someone else want
> to tackle it?
>
> 3. Yes, this is a bug too:
>
> >> create table test ( a datetime default 'now'::text,...)
> > Parser complains:
> > ERROR: parser: parse error at or near "'"
> > Does this considered as a bug or feature ?
>
> See above --- reverse-parsing of this construct is wrong. I have
> no intention of fixing the reverse parser; I want to get rid of it
> entirely.
>
> regards, tom lane
>

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83