Create function if records exist then do nothing else run some code in postgres

Question

I need to run a function that will run a bunch of INSERT queries. The only thing is I need to make sure this process hasn't already happened. The problem is I can't quite get the syntax correct in my if then statement.

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!!!';

score 2 · Accepted Answer · answered Sep 01 '17 at 11:55

try:

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

otherwise if you want EXISTS, add brackets:

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

or probably what you initially wanted:

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

the idea is to have boolean next to IF

Create function if records exist then do nothing else run some code in postgres

1 Answers1