Sql Commands

commands.sql

---DDL:
DROP TABLE IF EXISTS chart_properties CASCADE;
CREATE TABLE chart_properties(
chart_id SERIAL PRIMARY KEY NOT NULL,
chart_x INTEGER NOT NULL,
chart_y INTEGER NOT NULL,
chart_row INTEGER NOT NULL,
chart_col INTEGER NOT NULL,
chart_title VARCHAR(50) NOT NULL
);

---DML:

INSERT INTO chart_properties (chart_x, chart_y, chart_row, chart_col, chart_title)
VALUES
(2, 2, 0, 1, 'Title 1'),
(3, 1, 0, 2, 'Title 2');

---Function:

CREATE OR REPLACE FUNCTION update_table_func(arrayofvalues jsonb)
RETURNS void AS $$
BEGIN
WITH source AS (SELECT * FROM jsonb_populate_recordset(NULL::chart_properties, $1::jsonb))
UPDATE chart_properties
SET (chart_id, chart_x, chart_y, chart_row, chart_col) = (source.chart_id, source.chart_x, source.chart_y, source.chart_row, source.chart_col)
FROM source
WHERE source.chart_id = chart_properties.chart_id;
END;
$$ LANGUAGE plpgsql;

-- Run the query in the console or through PG_ADMIN
SELECT update_table_func('[{"chart_id":1,"chart_x":4,"chart_y":4,"chart_row":4,"chart_col":4},{"chart_id":2,"chart_x":3,"chart_y":3,"chart_row":3,"chart_col":3}]');