trigger function的问题，或者 postgreSQL下的upsert！？？

Postgre_New

大家好：
因为作业的关系刚刚接触数据库
主要希望完成这样一件事情：

表如下：
=================================
CREATE TABLE download
(
  id integer NOT NULL DEFAULT nextval('seq'::regclass),
  songid integer NOT NULL,
  counter integer NOT NULL,
  weeknumber integer NOT NULL,
  CONSTRAINT download_pkey PRIMARY KEY (id ),
  CONSTRAINT download_songid_fkey FOREIGN KEY (songid)
      REFERENCES song (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE download
  OWNER TO postgres;
==================================
想在程序中使用 ：
==================================
insert into download (weeknumber, singerid, counter) values ((SELECT EXTRACT(WEEK FROM (SELECT CURRENT_TIMESTAMP))),

?, 1)
==================================
进行插入操作， 逻辑是：如果有 weeknumber 和 singerid 组合存在的话，就对那条已经存在的记录中的 counter 字段进行 +1 操

作；如果不存在就执行插入操作。
所以希望写一个触发器，再程序中只用上面的代码，具体是 INSERT 还是 UPDATE 都有数据库来判断。但是
我自己的触发器完全的不工作，还是贴出来给大家批评批评吧！
===================================
CREATE OR REPLACE FUNCTION download_counter()
  RETURNS trigger AS
$BODY$
DECLARE
tmpid INTEGER;
BEGIN
SELECT id INTO tmpid FROM download WHERE songid=NEW.songid AND weeknumber=NEW.weeknumber;
IF NOT FOUND THEN
RETURN NEW;
ELSE
UPDATE download SET counter=counter+1 WHERE id=NEW.id;
RETURN NEW;
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION download_counter()
  OWNER TO postgres;
===================================
CREATE TRIGGER update_or_insert
  BEFORE INSERT
  ON download
  FOR EACH ROW
  EXECUTE PROCEDURE download_counter();
===================================
知道MySQL里有upsert 貌似可以实现类似的功能，但是postgre里面好像没有。。。。
不只有那位大侠可以帮忙解决下这个问题，非常感谢了 ！

Postgre_New

好吧！在经历了将近4个小时的试错法后，终于解决了：
===========================================
CREATE OR REPLACE FUNCTION download_counter()
  RETURNS trigger AS
$BODY$
DECLARE
tmpid INTEGER;
tmpcounter INTEGER;
tmpweeknumber INTEGER;
tmpsong INTEGER;
BEGIN
SELECT id,songid,counter,weeknumber INTO tmpid,tmpsong,tmpid,tmpcounter,tmpcounter FROM download WHERE

songid=NEW.songid AND weeknumber=NEW.weeknumber;
IF NOT FOUND THEN
RETURN NEW;
ELSE
UPDATE download SET counter=counter+1;
END IF;
RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION download_counter()
  OWNER TO postgres;
========================================

问题果然是处在 RETURN type 上面，不知道那位打给可以给详解一下这个 OLD和NEW 到底指的是什么！！！谢谢了！

Postgre_New

不好意思！少了点东西！...
======================================

CREATE OR REPLACE FUNCTION download_counter()
  RETURNS trigger AS
$BODY$
DECLARE
tmpid INTEGER;
tmpcounter INTEGER;
tmpweeknumber INTEGER;
tmpsong INTEGER;
BEGIN
SELECT id,songid,counter,weeknumber INTO tmpid,tmpsong,tmpid,tmpcounter,tmpcounter FROM download WHERE

songid=NEW.songid AND weeknumber=NEW.weeknumber;
IF NOT FOUND THEN
RETURN NEW;
ELSE
UPDATE download SET counter=counter+1 WHERE songid=NEW.songid AND weeknumber=NEW.weeknumber;
END IF;
RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION download_counter()
  OWNER TO postgres;

renxiao2003

使用程序接口就可以了啊。