Inserting into a table a row represented by JSON

Question

I've been laboring away trying to create a procedure which can data JSONB (or JSON, doesn't really matter), a table, and convert the JSON to a record and insert that record into the table. This is (roughly) the closest I've come to getting a functional prototype:

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, table_type anyelement) RETURNS VOID
    SECURITY INVOKER
    LANGUAGE plpgsql
AS $$
    DECLARE
        record RECORD;
        record_type RECORD;
    BEGIN
        EXECUTE format('SELECT x.* FROM (SELECT a.* FROM CAST(NULL as %s) a) x', pg_typeof(table_type))
            INTO record_type;
        record := jsonb_populate_record(record_type, data);
        INSERT INTO users SELECT (record).* RETURNING id;
    END;
$$;

So let's say that I do the following:

SELECT fn_alter_entity('{"a": "b"}'::JSONB, users);

I receive the following error:

ERROR: record type has not been registered
SQL state: 42809
Context: SQL statement "INSERT INTO users SELECT (record).* RETURNING id"
PL/pgSQL function fn_alter_entity(jsonb,anyelement) line 9 at SQL statement

Now, obviously the reason I'm receiving that error is that record_type is not actually a real record type. Now, normally we would just do something like jsonb_populate_record(data, NULL::users) or jsonb_populate_record(data, NULL::events) or the like. But what if I want that record type to be dynamic? Is there any possibility? I feel like I'm on the verge of nailing this down, but just can't quite get over the hump. All I want to do is be able to transform JSON into a record.

Answer 1

I think you want something like this.

CREATE OR REPLACE FUNCTION fn_alter_entity(data JSONB, t TEXT )
  RETURNS VOID
  SECURITY INVOKER
AS $$
    BEGIN
        EXECUTE FORMAT(
          'INSERT INTO %I SELECT * FROM jsonb_populate_record( null::%I, $1);',
          t,
          t
        )
        USING data;
    END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TABLE foo ( a int );
CREATE TABLE bar ( a int, b text );

SELECT fn_alter_entity( '{"a": 5}', 'foo' );
SELECT fn_alter_entity( '{"a": 42}', 'foo' );
SELECT fn_alter_entity( '{"a": 5, "b": "foo"}', 'bar' );
SELECT fn_alter_entity( '{"a": 42, "b": "baz"}', 'bar' );

test=# TABLE foo;
 a  
----
  5
 42
(2 rows)

test=# TABLE bar;
 a  |  b  
----+-----
  5 | foo
 42 | baz
(2 rows)

But seeing where you may be going, I would highly suggest you look into PostgREST.