title: Passing a row type dynamically to jsonb_populate_recordset
body: <p>I'm working on some code to unwind data packed into a JSONB with a regular format for passthrough to another system. </p>
  
  <p>-- Postgres 11.4.<br>
  -- The format of the JSON is knowable in advance, it's specified in the record.<br>
  -- I've got a custom type for the JSON format defined.</p>
  
  <p>Here's the stripped down passthrough table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS data.domo_passthrough (
      id           uuid NOT NULL DEFAULT extensions.gen_random_uuid (),
      type_name    text NOT NULL DEFAULT NULL, 
      report_data  jsonb NOT NULL DEFAULT '{}',
  
  CONSTRAINT domo_passthrough_id_pkey
      PRIMARY KEY (id)
  );
  </code></pre>
  
  <p>The <code>report_data</code> field holds the packed data, the <code>type_name</code> field includes the name of the format, like this one:</p>
  
  <pre><code>create type data.table_stats_type as (
      table_name text, 
      record_count integer,
      table_number integer)
  </code></pre>
  
  <p>This code works properly to unpack the data into the three columns defined in the type:</p>
  
  <pre><code>select expanded_data.*
    from domo_passthrough,
         jsonb_populate_recordset(null::table_stats_type, report_data) as expanded_data;
  </code></pre>
  
  <p>What I'd like to do, if possible, is to pass in the type_name as an argument to <code>jsonb_populate_recordset</code>:</p>
  
  <pre><code>select expanded_data.*
    from domo_passthrough,
         jsonb_populate_recordset(null::type_name, report_data) as expanded_data;
  </code></pre>
  
  <p>This does <em>not</em> work as <code>null::type_name</code> returns an error:</p>
  
  <pre><code>ERROR:  type "type_name" does not exist
  LINE 3:        jsonb_populate_recordset(null::type_name, report_data..
  </code></pre>
  
  <p>Fair. The type_name is a text, not a literal. I'm hoping that there is a tidy way to do what I'm after. (I understand that it's on me at that point to not screw up the type name assignments in the data.) Possibilities:</p>
  
  <p>-- Some kind of <code>::casting</code> operation, but I can't figure out what type I'd use between <code>null::</code> and <code>type_name</code>. I've found the <code>pg_typeof</code> function, but don't see a way to apply it to build in code.</p>
  
  <p>-- Dynamic SQL in a function. This seems like it should be possible, but I'm hoping for something simpler.</p>
  
  <p>-- Some other feature or option I'm unaware of.</p>
  
  <h1>RETURN SETOF Function?</h1>
  
  <p>Thanks for the comment, I figured I'd try writing a function...and I got stumped. It's always tricky to get dynamic results out of SQL. I'm not complaining about that...but this is one time I'd like to have a generalized tool. It <em>sounds like</em> <code>RETURN SETOF RECORD</code> ought to work, but I haven't succeeded. I've look through a fair few threads and the docs, but I'm still stumped. Here's a PG/pgSQL function as I couldn't get the pure SQL version to work:</p>
  
  <pre><code>DROP FUNCTION IF EXISTS api.domo_passthrough_expand (text, jsonb);
  CREATE FUNCTION api.domo_passthrough_expand(type_name text, packed_data jsonb) 
      RETURNS SETOF record AS
  $BODY$
  BEGIN
  
      RETURN QUERY EXECUTE FORMAT
     'select *
        from jsonb_populate_recordset(null::%1$I, %2$I) as expanded_data;',
             type_name,
             packed_data;
  END;
  $BODY$
  LANGUAGE plpgsql;
  
  ALTER FUNCTION api.domo_passthrough_expand(text, jsonb) OWNER TO user_bender;
  
  select expanded.* 
  
    from domo_passthrough,
          domo_passthrough_expand(domo_passthrough.type_name,domo_passthrough.report_data)
  
  ERROR:  a column definition list is required for functions returning "record"
  LINE 4:      domo_passthrough_expand(domo_passthrough.type_name,domo...
  </code></pre>
  
  <p>Is there a way to do my expansion generically with the help of a function? If there isn't, I can approach things another way, but I'd prefer to have it all Postgres-side.       </p>