Advertisement
hackerboxes

postgresql function

Apr 2nd, 2013
275
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.85 KB | None | 0 0
  1. PostgreSQL如何满足已经存在则更新, 不存在则插入的需求.
  2. A :
  3. digoal=> create table exists_test (id int primary key,info text);NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "exists_test_pkey" for table "exists_test"CREATE TABLEdigoal=> create or replace function insert_exists_test(i_id int,i_info text) returns void as $BODY$declarebeginperform 1 from exists_test where id=i_id;if not found theninsert into exists_test(id,info) values (i_id,i_info);return;elseupdate exists_test set info=i_info where id=i_id;return;end if;exceptionwhen others thenraise exception 'Insert exists_test(id,info) values(%,%) error.',i_id,i_info;return;end;$BODY$ language plpgsql;digoal=> select insert_exists_test(1,'digoal');digoal=> select insert_exists_test(1,'adigoal');digoal=> select * from exists_test ; id | info ----+--------- 1 | adigoal
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement