Call UPSERT function multiple times to different inputs

Question

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

Answer 1

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.

Related:

• Issue with composite type in an UPSERT function
• How do I do an UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
• Optimizing bulk update performance in PostgreSQL
• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

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 2

You can call the function multiple times like the following

select merge_vehicles (2335, 55, '246BDH', '246BDH', '811', 1);
select merge_vehicles (2336, 55, '038THX', '038THX', '831', 1);