Using execute with if exists

Question

How would you write a function in postgresql with execute and if exists statements?

CREATE FUNCTION replace_value(var_id char, var_data text, table_name char) RETURNS void AS $$
BEGIN
    IF EXISTS EXECUTE'(SELECT id FROM ' ||table_name|| ' WHERE id = '||var_id||')'

    THEN    EXECUTE 'UPDATE ' ||table_name||' 
            SET (id, data) = '||(var_id, var_data)||';'

    ELSE    EXECUTE 'INSERT INTO ' ||table_name||' (id, data) 
            VALUES '||(var_id, var_data)||';'

    END IF;
    RETURN;

END;
$$ LANGUAGE plpgsql;

I would also use table_name as an argument passed to the function and some variables in this example 'var_id' and 'var_data'. I know that using table name in postgresql function is only possible when using the execute statement.

Pavel Stehule · Accepted Answer · 2014-01-06 11:20:28Z

2

The EXECUTE is plpgsql statement, and you cannot to merge two statements together. Your code has other two problems - SQL injection vulnerability and race conditions.

One variant (should fail, when more clients will try insert same data)

DECLARE rc int;
BEGIN
  EXECUTE format('UPDATE %I SET data=$1 WHERE id=$2', table_name)
    USING data, var_id;
  GET DIAGNOSTICS rc = ROW_COUNT;
  IF rc = 0 THEN
    EXECUTE format('INSERT INTO %I(id, data) VALUES($1,$2)', table_name)
      USING var_id, data;
  END IF;
END;

or, some like did you do:

DECLARE rc int;
BEGIN
  EXECUTE format('SELECT id FROM %I WHERE id=$1 FOR UPDATE', table_name)
    USING var_id;
  GET DIAGNOSTICS rc = ROW_COUNT;
  if rc = 0 THEN
    EXECUTE format('INSERT INTO %I(id, data) VALUES($1,$2)', table_name)
      USING var_id, data;
  ELSE
    EXECUTE format('UPDATE %I SET data=$1 WHERE id=$2', table_name)
      USING data, var_id;
  END IF;
END

answered Jan 6, 2014 at 11:20

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ZiupeX Over a year ago

it`s really work thank you. Which example is better to use? First one or second one? You wrote one variant (should fail, when more clients will try insert same data) that mean, the second way is better?

Pavel Stehule Over a year ago

first variant should be little bit faster - both variants can fail when some condition will be true. Choose one or second depends on transaction isolation level and on load - how often are conflicts. Cannot to say, what is better - I prefer first, but I second variant can be better in some specific use cases.

Collectives™ on Stack Overflow

Using execute with if exists

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related