Postgres: inserting dynamic number of columns and values in table

If you are allowed to pass that information as a structured JSON value, this gets quite easy. Postgres has a feature to map a JSON value to a table type using the function json_populate_record

Sample table:

create table some_table 
( 
  id integer primary key, 
  some_name text, 
  some_date date, 
  some_number integer
);

The insert function:

create function do_insert(p_data text)
 returns void
as 
$$
  insert into some_table (id, some_name, some_date, some_number)
  select (json_populate_record(null::some_table, p_data::json)).*;
$$
language sql;

Then you can use:

select do_insert('{"id": 42, "some_name": "Arthur"}');
select do_insert('{"id": 1, "some_value": 42}');

Note that columns that are not part of the passed JSON string are explicitly set to NULL using this approach.

If the passed string contains column names that do not exist, they are simply ignored, so

select do_insert('{"id": 12, "some_name": "Arthur", "not_there": 123}');

will ignore the not_there "column".

Online example: https://rextester.com/JNIBL25827

Edit

A similar approach can be used for updating:

create function do_update(p_data text)
 returns void
as 
$$
  update some_table 
     set id = t.id,
         some_name = t.some_name, 
         some_date = t.some_date, 
         some_number = t.some_number
  from json_populate_record(null::some_table, p_data::json) as t;
$$
language sql;

or using insert on conflict to cover both use cases with one function:

create function do_upsert(p_data text)
 returns void
as 
$$
  insert into some_table (id, some_name, some_date, some_number)
  select (json_populate_record(null::some_table, p_data::json)).*
  on conflict (id) do update
     set id = excluded.id,
         some_name = excluded.some_name, 
         some_date = excluded.some_date, 
         some_number = excluded.some_number
$$
language sql;