Last active
July 17, 2023 12:37
-
-
Save vbilopav/0829155a0c8523e10a93b605621cc483 to your computer and use it in GitHub Desktop.
Script to create a postgresql table from markdown table (pasted as variable)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
do | |
$$ | |
declare | |
_table_name text = 'md_temp'; | |
_md text = ' | |
| Command | Description | | |
| --- | --- | | |
| `git status` | List all *new or modified* files | | |
| `git diff` | Show file differences that **havent been** staged | | |
'; | |
_line text; | |
_i integer; | |
_exp text; | |
_array text[]; | |
begin | |
_i = 0; | |
foreach _line in array string_to_array(_md, E'\n') loop | |
-- | |
-- skip empty lines | |
-- | |
if trim(_line) = '' then | |
continue; | |
end if; | |
_i = _i + 1; | |
-- | |
-- header line, create a table | |
-- | |
if _i = 1 then | |
_array = string_to_array(_line, '|'); | |
_exp = 'create temp table ' | |
|| _table_name | |
|| ' (' | |
|| ( | |
select string_agg(replace(lower(trim(a)), ' ', '_'), ' text, ') | |
from unnest(_array) a | |
where trim(a) <> '' | |
) | |
|| ' text);'; | |
raise info '%', _exp; | |
execute format('drop table if exists %s;', _table_name); | |
execute _exp; | |
-- | |
-- separator line num. 2, skip | |
-- | |
elsif _i = 2 then | |
continue; | |
-- | |
-- inserts | |
-- | |
else | |
_array = string_to_array(_line, '|'); | |
-- skip empty line | |
if not exists(select 1 from unnest(_array) a where trim(a) <> '')b then | |
continue; | |
end if; | |
_exp = 'insert into ' | |
|| _table_name | |
|| ' values (' | |
|| ( | |
select string_agg(case when trim(a) = '' then 'NULL' else '''' || trim(replace(replace(a, '**', ''), '`', '')) || '''' end, ', ') | |
from unnest(_array) a | |
where trim(a) <> '' | |
) | |
|| ');'; | |
execute _exp; | |
end if; | |
end loop; | |
end; | |
$$; | |
select * from md_temp; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment