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.