PostgreSQL: Insert dynamic columns and values from jsonb argument

Question

This question relates to Update dynamic column names using keys from jsonb argument and my code is based on the answer provided there by https://dba.stackexchange.com/users/3684/erwin-brandstetter

I'm trying to write a generic function that inserts data into a table with multiple data types from a jsonb record. How can I dynamically cast each value to the data type for the respective column?

I'm using PostgreSQL version 15.2

A typical call to this function would be:

json_data = '{
 "text_field": "Some text",
 "numeric_field": 17,
 "datetime_field": "2023年04月24日 11:06:50.36758-04",
 "boolean_field": true
}'
SELECT * FROM insert_jsonb('schema_name', 'table_name', json_data);

My function:

CREATE OR REPLACE FUNCTION insert_from_jsonb(
 sch_name text,
 tbl_name text,
 js jsonb,
 out new_id uuid;
) AS
$func$
DECLARE
 _sql text;
BEGIN
 SELECT INTO _sql
 'INSERT INTO ' || sch_name || '.' || tbl_name || ' ('
 || string_agg(format('%I', key), ', ') || ') VALUES ('
 || string_agg(format('%L', value), ', ') || ')' -- < how can I cast each value?
 || ' RETURNING uuid'
 FROM jsonb_each_text(js);
 IF _sql IS NOT NULL THEN
 -- RAISE NOTICE '%', _sql; -- uncomment for debugging
 EXECUTE _sql INTO new_id; -- comment out for debugging
 END IF;
END
$func$ LANGUAGE plpgsql ;

Answer 1

You concatenate values as (untyped) string literals into the query string, so you don't need to add an explicit cast. Postgres will coerce each value with an assignment cast to the data type of the target column automatically. (Must be valid for the type, of course. Else you get an error. But an explicit cast wouldn't get you the same result.)

Your posted function works as is. I would only suggest an outer format() to tidy up:

CREATE OR REPLACE FUNCTION insert_from_jsonb(sch_name text
 , tbl_name text
 , js jsonb
 , OUT new_id uuid)
 LANGUAGE plpgsql AS
$func$
DECLARE
 _sql text;
BEGIN
 SELECT INTO _sql
 format('INSERT INTO %I.%I (%s) VALUES (%s) RETURNING uuid'
 , sch_name, tbl_name
 , string_agg(format('%I', key), ', ')
 , string_agg(format('%L', value), ', ') -- < no explicit cast needed
 )
 FROM jsonb_each_text(js);
 IF _sql IS NULL THEN
 RAISE WARNING '%', 'Your text here';
 ELSE
 -- RAISE NOTICE '%', _sql; -- uncomment for debugging
 EXECUTE _sql INTO new_id; -- comment out for debugging
 END IF;
END
$func$;

• Russell Turner: Thank you Erwin, much appreciated!

Answer 2

See the updated insert function in Erwin's answer. I decided to merge the insert with the update function from the related question: Update dynamic column names using keys from jsonb argument. This function updates an existing record if the "id" is provided, or generates a new UUID and inserts a new record if no "id" is provided. Either way returns the resource ID.

CREATE OR REPLACE FUNCTION upsert_from_jsonb(
 sch_name text,
 tbl_name text,
 js jsonb,
 out res_id uuid)
LANGUAGE plpgsql AS
$func$
DECLARE
 new_js jsonb;
 _sql text;
BEGIN
 SELECT (js ->> 'id')::uuid INTO res_id;
 IF res_id IS NOT NULL THEN
 new_js := js - 'id';
 SELECT INTO _sql
 format('UPDATE %I.%I SET %s WHERE id = %L',
 sch_name, tbl_name,
 string_agg(format('%I = %L', key, value), ', '),
 res_id
 )
 FROM jsonb_each_text(new_js);
 ELSE
 res_id := gen_random_uuid();
 new_js := js || jsonb_build_object('id', res_id);
 SELECT INTO _sql
 format('INSERT INTO %I.%I (%s) VALUES (%s)',
 sch_name, tbl_name,
 string_agg(format('%I', key), ', '),
 string_agg(format('%L', value), ', ')
 )
 FROM jsonb_each_text(new_js);
 END IF;
 IF _sql IS NULL THEN
 RAISE WARNING 'No sql for %I.%I on %s', sch_name, tbl_name, js;
 ELSE
 -- RAISE NOTICE '%', _sql; -- uncomment for debugging
 EXECUTE _sql; -- comment out for debugging
 END IF;
END
$func$;