pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: pere@td.org.uit.no
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Problems with default date 'now'
Date: Wed, 10 Jun 1998 15:09:37 +0300
Message-ID: <l03110703b1a4267df0d0@[147.233.159.109]> (raw)
In-Reply-To: <199806101143.NAA11630@sleeper.games.no>

At 14:43 +0300 on 10/6/98, Petter Reinholdtsen wrote:


> Nope, that did not work.
>
> I get this reply from psql:
>
>   WARN:parser: parse error at or near "current_date"
>
> What is wrong.  Is this something new in PostgreSQL after v6.2.1?

Yes, it's something new. I still work with 6.2.1, so here's the deal:

Using a constant default value for a column causes the constant to be
evaluated once, at the creation of the table. That value is then kept with
the table schema, which means each row will be stamped with the same date.

In order to avoid that, you have to use a function as a default value.
Functions are evaluated each time a column is created. For this purpose, I
created an SQL function like this:

CREATE FUNCTION current_datetime() RETURNS datetime
AS 'SELECT ''now''::datetime'
LANGUAGE 'sql';

And I define the table as (in my case):

CREATE TABLE session
(
        session         int4
                        DEFAULT nextval( 'sess_no' )
                        NOT NULL,
        created         datetime
                        DEFAULT current_datetime() -- See here
                        NOT NULL,
        webuser         char(30)
);

You can define the function once, and use it for all the applications using
the same database.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (7+ messages)  latest in thread

Message-ID: <l03110703b1a4267df0d0@[147.233.159.109]>
Permalink:  ../l03110703b1a4267df0d0@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03110703b1a4267df0d0@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, pere@td.org.uit.no
  Subject: Re: [SQL] Problems with default date 'now'
  In-Reply-To: <l03110703b1a4267df0d0@[147.233.159.109]>
  References: <199806101143.NAA11630@sleeper.games.no> <l03110703b1a4267df0d0@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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