Skip to content
Advertisement

How to update several rows with postgresql function

I have a table representing an object:

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):

PostgreSQL fiddle

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