Postgresql – Interactive INSERT / UPDATE function to implement UPSERT

functionsplpgsqlpostgresqlpostgresql-9.1upsert

I have a function in PostgreSQL 9.1 that I want client applications to be passing a record object and the function will detect if it's an insert or an update and return the operation done, i.e "1 record successfully added" or " Record with PK [wherever] was successfully updated" or "An error occurred, field X cannot be null" or "any error that it gets".

I want to avoid an expensive CURSOR and an even more expensive EXCEPTION clause. I also want it to be optimized for speed. The function should consider concurrency because there will be more than 50 clients connected to the database at the same time, each updating/inserting (calling the same function). This is what I have currently:

CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.test)
  RETURNS text AS
$BODY$
DECLARE 
myoutput text :='Nothing has occured';
BEGIN
    update netcen.test set 
    tes=myobj.tes,
    testname=myobj.testname  WHERE testkey=myobj.testkey;
IF FOUND THEN
        myoutput:= 'Record with PK[' || myobj.testkey || '] successfully updated';
        RETURN myoutput;
    END IF;
    BEGIN
        INSERT INTO netcen.test values(myobj.testkey,
    myobj.tes,
    myobj.testname);
    myoutput:= 'Record successfully added';
    EXCEPTION WHEN null_value_not_allowed THEN
        RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    WHEN not_null_violation THEN
        RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    WHEN unique_violation THEN
    RAISE NOTICE 'something has just occured';
    myoutput:= 'A field was null';
    END;
    RETURN myoutput;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I know this function can be simplified to a simple and shorter writable CTE function of SQL language. But those have a race condition. And I don't know how to capture errors and report the operation done as output from it.

Can this function be simplified or complicated but handle all my concerns?

Best Answer

If you want to catch NULL violations as well you have to cover the UPDATE as well, which makes the function a little more expensive.

SET search_path = netcen;

CREATE OR REPLACE FUNCTION fun_test(myobj test, OUT myoutput text)
  RETURNS text AS
$func$
BEGIN
   UPDATE test
   SET    tes = myobj.tes,
          testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      myoutput := 'UPDATE successfull. testkey = ' || myobj.testkey;
      RETURN;
   END IF;

   INSERT INTO test
   SELECT (myobj).*;
   myoutput := 'INSERT successfull. testkey = ' || myobj.testkey;

EXCEPTION
WHEN null_value_not_allowed THEN
   RAISE NOTICE 'null_value_not_allowed occurred.';
   myoutput := 'A field was null.';

WHEN not_null_violation THEN
   RAISE NOTICE 'not_null_violation occurred.';
   myoutput:= 'A field was null.';

WHEN unique_violation THEN
   RAISE NOTICE 'unique_violation occurred.';
   myoutput:= 'Duplicate value.';
END
$func$ LANGUAGE plpgsql;

Using an OUT parameter and made some simplifications and clarifications.

I would just let the regular EXCEPTION happen. People are not supposed to enter NULL values for NOT NULL columns. This way, the UPDATE can run outside the EXCEPTION block.

Answer

The error occurs here:

CASE tmp_code
    WHEN COALESCE(tmp_code,0)=0 THEN

Works like this:

CASE WHEN COALESCE(tmp_code,0)=0 THEN

You mixed two different syntax variants of PL/pgSQL CASE ("simple" vs. "searched") in an incompatible way.

There is another error:

update netcen.test set 
test=myobj.test,
testname=myobj.testname  WHERE testkey=myobj.testkey;

Did you mean:

UPDATE test
SET    tes = myobj.tes
     , testname = myobj.testname
WHERE  testkey = myobj.testkey;

There is also no need for CREATE TYPE netcen.testobj .... You can use the table name netcen.test as type name.

You really want to "UPSERT"

In Postgres 9.5 or later use the new UPSERT (INSERT ... ON CONFLICT DO UPDATE). Like:

UPSERT with ON CONFLICT using values from source table in the UPDATE part

For older versions you can emulate an UPSERT. The simple form in plpgsql, without concurrency:

CREATE OR REPLACE FUNCTION fun_test(myobj testobj)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN    
   UPDATE test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN FALSE;
   ELSE
      INSERT INTO test SELECT (myobj).*;
      RETURN TRUE;
   END IF;
END
$func$;

Could just be plain SQL with a data-modifying CTE:

WITH my_row(testkey, tes, testname) AS (
    SELECT 1::smallint, 'khaendra@me.net', 'khaendra'
    )
, u AS (
    UPDATE test t
    SET    tes = m.tes
         , testname = m.testname
    FROM   my_row m
    WHERE  t.testkey = m.testkey
    RETURNING t.testkey
    )
INSERT INTO test (testkey, tes, testname)
SELECT * FROM my_row
WHERE  NOT EXISTS (SELECT FROM u);

The time window for a possible race condition is extremely tiny with this form (single combined statement). If concurrency is still an issue (heavy concurrent write load), then ...

Your function reviewed

If the function returns, the row has been inserted or updated. The only other way is an EXCEPTION of a different kind. The returned value true is just noise. (Might be more interesting to return true for INSERT and false for UPDATE.) So I simplified:

CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE netcen.test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN true;
   END IF;

   BEGIN
      INSERT INTO netcen.test 
      SELECT (myobj).*;  -- simpler form, parenthesis needed.

   EXCEPTION WHEN unique_violation THEN   -- cleaner
      UPDATE netcen.test
      SET    tes = myobj.tes
           , testname = myobj.testname
      WHERE  testkey = myobj.testkey;
   END;
   RETURN true;
END
$func$;

Best Answer

Related Solutions

PostgreSQL UPSERT – Idiomatic Implementation Guide

PostgreSQL UPSERT Function – Composite Type Issue

Answer

You really want to "UPSERT"

Your function reviewed

Related Question