I have a table that store configurations and I need to use them in a query. The starting table has its value stored as this text:

---
'1':
  '1': New Year's Day
  '6': Epiphany
'11':
  '1': All the Saints
'12':
  '24': Christmas Eve
  '25': First Christmas Day
  '26': Second Christmas Day
  '31': New Year's Eve
'5':
  '1': International Workers' Day
'8':
  '15': Mid-August

In the second row, for example, I have the month, end in the third row, with a two space indentation, I have the number of the day with its description separated with a colon.

I want to have it in a table like this one:

I put an example in db<>fiddle.

CodePudding user response：

More dark regex sorcery to transform text data into a json format.
So that the data can be extracted via json methods.

INSERT INTO holidays (holiday,description)
select 
  make_date(2018, l2.Month, l2.Day) as holiday
, l2.Name as description
from test
cross join lateral (
  select 
  regexp_replace(
   regexp_replace(
    regexp_replace(
     regexp_replace(wh, 
      '\W (\d )\W (\w.*)$',' {"day":"\1","name":"\2"},','gm')
    , '^''(\d )'':', '{"month":"\1","days":[','gm')
    , '\},(\s*)(?=\{"month"|$)', '}\1]},','g')
    , '^.*?(\{.*\}).*$', '[\1]')::json as whjs
) l1
cross join lateral (
  select 
    (mjs.value->>'month')::int as Month
  , (djs.value->>'day'):: int as Day
  , djs.value->>'name' as Name
  from json_array_elements(l1.whjs) mjs
  cross join lateral json_array_elements(mjs.value->'days') djs
) l2

9 rows affected

select * from holidays;

id	description	holiday
1	New Year's Day	2018-01-01
2	Epiphany	2018-01-06
3	All the Saints	2018-11-01
4	Christmas Eve	2018-12-24
5	First Christmas Day	2018-12-25
6	Second Christmas Day	2018-12-26
7	New Year's Eve	2018-12-31
8	International Workers' Day	2018-05-01
9	Mid-August	2018-08-15

Test on db<>fiddle here