PostgreSQL – How to Call UPSERT Function Multiple Times with Different Inputs

functionspostgresqlupsert

I have a function:

merge_vehicles(vid, cid, vname, reg_no, name, name_1st)

Can i call it multiple times on inputs like

(2335, 55, '246BDH', '246BDH', '811', 1),
(2336, 55, '038THX', '038THX', '831', 1),
....

The function is an UPSERT implementation returning void. This is meant to be a warehouse database (fact table). We have a live database that gets more than 1000 inputs per hour. We want to use this command to merge our data with the new data.

It is a table function. (I have defined alike functions to every table we have – that's 5.) Result is, it will update existing rows, if they exist, otherwise inserts new row.

Postgres version is 9.3.

Function is a version of the accepted answer here:

Idiomatic way to implement UPSERT in PostgreSQL

Best Answer

UPSERT 1 row at a time with function

A "table-function" is a function returning a set of rows (acting like a table when called with SELECT * FROM myfunc()). What you have is not a table-function. Since nothing is returned you can use a simple call:

SELECT merge_vehicles(vid, cid, vname, reg_no, name, name_1st)
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names

While that works, upserting 1 row at a time is very inefficient for big sets.

Bulk UPSERT

Assuming (vid, cid) is the primary key of our table test (information missing), I suggest a data-modifying CTE for the UPSERT after taking out an EXCLUSIVE LOCK on the table.

The EXCLUSIVE LOCK still allows concurrent transactions to read from the table, but no writes. It is only necessary if you actually can have concurrent transactions writing to the same table.

BEGIN;

LOCK TABLE test IN EXCLUSIVE MODE;

-- Or use a temp table for new rows. See below.
WITH my_rows(vid, cid, vname, reg_no, name, name_1st) AS (
   VALUES
      (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
     ,(2336, 55, '038THX', '038THX', '831', 1)
     , ...
   )
, upd AS (
    UPDATE test t
    SET   (  vname,   reg_no,   name,   name_1st)
        = (m.vname, m.reg_no, m.name, m.name_1st)
    FROM   my_rows m
    WHERE  t.vid = m.vid        -- match on PK columns
    AND    t.cid = m.cid
    RETURNING t.vid, t.cid      -- return only PK columns
    )
INSERT INTO test
      (  vid,   cid,   vname,   reg_no,   name,   name_1st)
SELECT m.vid, m.cid, m.vname, m.reg_no, m.name, m.name_1st
FROM   my_rows m
LEFT   JOIN upd USING (vid, cid)
WHERE  u.vid IS NULL;           -- only remaining rows

COMMIT;                         -- releases all locks

If the list is long, consider a temp table populated with COPY to replace the CTE my_rows and its VALUES expression.

Call set-returning function multiple times in one SELECT

^{(Before update to the question)}

Assuming we are dealing with a set-returning function (a.k.a. "table-function").

You can use a VALUES expression for literal input of a derived table of parameters (or any other derived or actual table in its place) and conveniently use a LATERAL JOIN (Postgres 9.3+) to get the united results in one query. You may want to order in some way ...

SELECT f.*
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names
     , merge_vehicles(vid, cid, vname, reg_no, name, name_1st) f;

The LATERAL JOIN is implicit with function calls in the FROM list. More details here:

How to turn JSON array into Postgres array?

Numeric literals in this example default to type integer and string literals become text. Add explicit casts if you need specific types.

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$;

Postgresql – Interactive INSERT / UPDATE function to implement UPSERT

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.