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$;

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$;