精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2010-11-09
最后修改:2010-11-09
MySQL中on duplicate key update username = VALUES(username),maxdate = VALUES(maxdate), 在PostgresSQL中如何实现呢?
MySQL中的语句: insert into db select distinct username ,mindate,maxdate from test_table on duplicate key update username = VALUES(username),maxdate = VALUES(maxdate);
PostgresSQL中实现: 步骤一:创建表 CREATE TABLE db (username TEXT, userid TEXT ,mindate TEXT ,maxdate TEXT);
步骤二:创建唯一索引 CREATE UNIQUE INDEX username_index ON db (username);
步骤三:创建更新函数 CREATE FUNCTION merge_db(uname TEXT, uid TEXT ,mind TEXT , maxd TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the username UPDATE db SET maxdate = maxd WHERE username = uname; IF found THEN RETURN; END IF; -- not there, so try to insert the username -- if someone else inserts the same useridconcurrently, -- we could get a unique-useridfailure BEGIN INSERT INTO db(username,userid,mindate,maxdate) VALUES (uname, uid, mind,maxd); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql;
步骤四:测试案例 select merge_db('user_one','279470','20101011','20101011'); select merge_db('user_two,'279470','20101011','20101012'); select merge_db('user_one','279470','20101011','20101012'); 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
浏览 2540 次