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.