2

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:

1
  • Obviously, you have to give more details. Is it a table-function? How is the return type defined? In what context? What is the desired result? Your version of Postgres? Nov 7, 2014 at 6:33

2 Answers 2

5

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:

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:

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

3
  • The function returns void and it is a upsert implementation. 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 and that new data
    – Hans
    Nov 7, 2014 at 11:11
  • @DaddyStrikes: That's a completely different problem, then. Consider the new answer. Nov 7, 2014 at 17:50
  • As I'm not really familiar with CTE or complex SQL sentences, Thanks! It was what I was looking for!
    – Hans
    Nov 7, 2014 at 20:50
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);
1
  • I though of I myself, but it aint gonna dig for my problem
    – Hans
    Nov 7, 2014 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.