On Thu, 2004-07-29 at 01:16, azah azah wrote:
> 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 '
' LANGUAGE 'plpgsql';
-- TABLE --
CREATE TABLE dtest (
id int primary key,
lm timestamp without time zone
CREATE TRIGGER dtest
BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
-- 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 
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 
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 
 The timestamp has changed for this record when we changed the id field.
 The timestamp also changes for the fluff field.
 We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it
In response to
pgsql-php by date
|Next:||From: Christopher Kings-Lynne||Date: 2004-07-29 14:53:04|
|Subject: Re: inet_aton in mysql, how to convert it to postresql?|
|Previous:||From: azah azah||Date: 2004-07-29 09:45:51|
|Subject: inet_aton in mysql, how to convert it to postresql?|