创建功能如果记录存在，那么什么也不做，请在Postgres中运行一些代码

Question

我需要运行一个可以运行一堆INSERT查询的函数。唯一的事情是我需要确保此过程尚未发生。问题是我在语句中不能完全正确地使语法正确。

CREATE OR REPLACE FUNCTION process.copyhalsetup_tonewitem(
integer,
integer)
RETURNS void AS
$BODY$
DECLARE
pItemID_Copy_From   ALIAS FOR $1;
pItemID_Copy_Too    ALIAS FOR $2;
BEGIN
IF SELECT EXISTS(SELECT 1 
FROM process.rmt_revs 
WHERE rmt_revs.item_id =  pItemID_Copy_Too) THEN
 raise notice 'a record exists. Code not executed';
 ELSE
 --a slew of INSERT statements
END IF

END;
$BODY$
 LANGUAGE plpgsql VOLATILE
  COST 100;
  ALTER FUNCTION process.copyhalsetup_tonewitem(integer, integer)
  OWNER TO postgres;
COMMENT ON FUNCTION process.copyhalsetup_tonewitem(integer, integer) IS 'This function is used to copy from an existing item HAL data to a new like item for which there may be a new item rev or item number change.  CAUTION: Only use this for NON-FAMILY MOLDS!!!';

Answer 1

尝试：

IF (SELECT count(1) 
FROM process.rmt_revs 
WHERE rmt_revs.item_id =  pItemID_Copy_Too) > 0 THEN

否则，如果您需要存在，请添加括号：

IF (SELECT EXISTS(SELECT 1 
FROM process.rmt_revs 
WHERE rmt_revs.item_id =  pItemID_Copy_Too)) THEN

或您最初想要的：

IF EXISTS(SELECT 1 
FROM process.rmt_revs 
WHERE rmt_revs.item_id =  pItemID_Copy_Too) THEN

这个想法是要在