title: Postgres Function to Populate Recordset and Returned ID
body: <p>I am trying to <code>INSERT</code> a record, return the ID, and then pass the ID to other tables and insert additional data. The data to be inserted is in JSON format.</p>
  
  <p>I am using a function call to insert the data, and while the create statement works, I am doing something wrong during the execution of it (so either a) I didn't write the statement properly or b) I didn't pass data in properly).</p>
  
  <p>The error I am getting is such:</p>
  
  <pre><code>ERROR: INSERT has more target columns than expressions
  SQL state: 42601
  Context: PL/pgSQL function insert_to_tables(jsonb,jsonb,jsonb) line 3 at SQL statement
  </code></pre>
  
  <p>Here is how my the tables look:</p>
  
  <pre><code>CREATE TABLE main_data(
    id SERIAL PRIMARY KEY,
    field_1 TEXT,
    some_time DATE
  );
  
  CREATE TABLE locale_data(
    locale_id SERIAL PRIMARY KEY,
    city TEXT,
    state TEXT,
    address TEXT,
    main_data_id INTEGER REFERENCES main_data(id)
  );
  
  CREATE TABLE demographic_data(
    demographic_id SERIAL PRIMARY KEY,
    age INT,
    ethnicity TEXT,
    main_data_id INTEGER REFERENCES main_data(id)
  );
  </code></pre>
  
  <p>I have a function like so to handle the data input and parsing:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION insert_to_tables (
  main_data_fields JSONB,
  locale_data_fields JSONB,
  demographic_data_fields JSONB,
  OUT new_user_id INTEGER
  )
  RETURNS integer AS $$
  BEGIN
     WITH ins AS (
        INSERT INTO main_data SELECT * FROM jsonb_populate_recordset(NULL::main_data, $1::jsonb)
        RETURNING id
        )
  
     INSERT INTO locale_data(city, state, address, main_data_id)
     SELECT i.id AS main_data_id, jsonb_populate_recordset(NULL::locale_data, $2::jsonb)
     FROM  ins i;
     INSERT INTO demographic_data(age, ethnicity)
     SELECT i.id AS main_data_id, jsonb_populate_recordset(NULL::demographic_data, $3::jsonb)
     FROM  ins i;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>To insert the data, I am calling the function like so:</p>
  
  <pre><code>select insert_to_tables(
  '{"field_1": "http://www.google.com", "some_time": "09-02-2019"}',
  '[{"city": "a city", "address": "123 fake road", "state": "CA"}, {"city": "little city", "address": "456 noname road", "state": "WA"}]',
  '[{"age": 45, "ethnicity": "Asian"}, {"age": "45", "ethnicity": "Egyptian"}]'
  );
  </code></pre>
  
  <p>My expected output should be the <code>main_data</code> table populated with 1 row, the <code>locale_data</code> table populated with 2 rows, and the <code>demographic_data</code> table populated with 2 rows.</p>
  
  <p>Each of the rows in the <code>locale</code> and <code>demographic</code> table belongs to and should reference the row in the <code>main_data</code> table like so:</p>
  
  <pre><code>id  |         field1_1        | some_time  |
  ----+-------------------------+------------+
   1  |  http://www.google.com  |  09-02-2019
  
  
  locale_id  |    city        |  state  |     address      | main_data_id
  -----------+----------------+---------+------------------+---------+
   1         |     a city     |   CA    | 123 fake road    |   1
   2         |   little city  |   WA    | 456 noname road  |   1
  
  locale_id  |    age      |    ethnicity   |  main_data_id
  -----------+-------------+----------------+------------------+
   1         |     45      |   Asian        |    1
   2         |     45      |   Egyptian     |    1 
  </code></pre>
  
  <p>I guess that my second set of <code>INSERT INTO</code> statements is miswritten, but I am not sure how to handle data insertion of JSON after returning an ID from another operation.</p>