X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 73DD2D1B209 for ; Thu, 29 Jul 2004 07:00:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 57741-06 for ; Thu, 29 Jul 2004 10:00:37 +0000 (GMT) Received: from mpls-qmqp-01.inet.qwest.net (mpls-qmqp-01.inet.qwest.net [63.231.195.112]) by svr1.postgresql.org (Postfix) with SMTP id 6EBF3D1B171 for ; Thu, 29 Jul 2004 07:00:33 -0300 (ADT) Received: (qmail 53161 invoked by uid 0); 29 Jul 2004 10:00:35 -0000 Received: from unknown (63.231.195.1) by mpls-qmqp-01.inet.qwest.net with QMQP; 29 Jul 2004 10:00:35 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-01.inet.qwest.net with SMTP; 29 Jul 2004 10:00:34 -0000 Date: Thu, 29 Jul 2004 04:01:52 -0600 Message-Id: <1091095312.27159.25.camel@localhost.localdomain> From: "Scott Marlowe" To: "azah azah" Cc: pgsql-php@postgresql.org Subject: Re: default timestamp in postresql In-Reply-To: <235b1a280407290016621836b4@mail.gmail.com> References: <235b1a280407290016621836b4@mail.gmail.com> Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/43 X-Sequence-Number: 2868 On Thu, 2004-07-29 at 01:16, azah azah wrote: > Hi, > I need some help to solve a problem regarding transfer the db from > mysql to postresql. > Previously in mysql table field as below , will be add automatic in > database when execute insert statement. > > = submittime timestamp NOT NULL > > But in postresql the timestamp cannot insert automatic when execute > insert statement. > How can I solve this problem instead of change the php code? If you need that field to always be updated whenever the row is changed, you can use a trigger. -- FUNCTION -- CREATE FUNCTION modtime () RETURNS opaque AS ' BEGIN new.lm :=''now''; RETURN new; END; ' LANGUAGE 'plpgsql'; -- TABLE -- CREATE TABLE dtest ( id int primary key, fluff text, lm timestamp without time zone ); --TRIGGER -- CREATE TRIGGER dtest BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE modtime(lm); -- SQL TESTS -- INSERT INTO dtest (id, fluff) VALUES (1,'this is a test'); INSERT INTO dtest (id, fluff) VALUES (2,'this is another test'); SELECT * FROM dtest; 1 | this is a test | 2003-04-02 10:33:12.577089 2 | this is another test | 2003-04-02 10:33:18.591148 UPDATE dtest SET id=3 WHERE id=1; 3 | this is a test | 2003-04-02 10:34:52.219963 [1] UPDATE dtest SET fluff='now is the time' WHERE id=2; SELECT * FROM dtest WHERE id=2; 2 | now is the time | 2003-04-02 10:38:06.259443 [2] UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3; SELECT * FROM dtest WHERE id=3; 3 | this is a test | 2003-04-02 10:36:15.45687 [3] [1] The timestamp has changed for this record when we changed the id field. [2] The timestamp also changes for the fluff field. [3] We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it