postges mysql_MySQL中on duplicate key update 在PostgesSQL中的实现方式

MySQL中on duplicate key update username = VALUES(username),maxdate = VALUES(maxdate),

在PostgresSQL中如何实现呢?

MySQL中的语句:

insert into db select distinct username ,mindate,maxdate from test_tableon 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');

分享到：

2010-11-09 12:46

浏览 1064

论坛回复 / 浏览 (0 / 2340)

分类:数据库

评论