2

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.

4
  • could you provide examples of what you're trying to do with input and output rather than your psuedo code that doesn't work? Jun 27, 2017 at 1:26
  • "record type to be dynamic" - how? I think you may be expecting more flexibility from postgres's type system than it can provide. Jun 27, 2017 at 1:35
  • @EvanCarroll, my examples, with input and output, don't work either. What I provided is not pseudocode, but rather a fully valid and compiling function. I have amended my question to provide further detail, but the original problem remains, detail or not. Jun 27, 2017 at 2:35
  • @CraigRinger I have amended my question to hopefully clarify a bit. If it's not possible then so be it. I was just hoping to avoid having to write multiple copies of the same procedure if I could help it! What better than to be able to re-use code right? Jun 27, 2017 at 2:36

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.