Skip to content
Advertisement

How to update several rows with postgresql function

I have a table representing an object:

id|field1|field2|field3
--|------|------|------
1 |   b  |  f   |  z
2 |   q  |  q   |  q

I want to pass several objects to pg function which will update corresponding rows. Now I see only one way to do it – to pass a jsonb with array of objects. For example: [{"id":1, "field1":"foo", "field2":"bar", "field3":"baz"},{"id":2, "field1":"fooz", "field2":"barz", "field3":"bazz"}]
Is this a best way to perform an update? And what is the best way to do it with jsonb input? I don’t really like the way to convert jsonb input to rows with select * from json_each('{"a":"foo", "b":"bar"}') and operating it. I would prefer some way to execute a single UPDATE.

Advertisement

Answer

You can do it in next way (using json_populate_recordset):

update test
set 
    field1 = data.field1,
    field2 = data.field2,
    field3 = data.field3
from (select * from json_populate_recordset(
  NULL::test, 
  '[{"id":1, "field1":"f.1.2", "field2":"f.2.2", "field3":"f.3.2"},{"id":2, "field1":"f.1.4", "field2":"f.2.5", "field3":"f.3.6"}]'
)) data
where test.id = data.id;

PostgreSQL fiddle

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement