如何根據json數據動態創建表？

我有一個作為文件獲取的 json，我已將其載入到 Postgres 數據庫中。這是json數據：

{
"tablename": "test",
"columns": [
   {
       "name": "field1",
       "datatype": "BigInt"
   },
   {
       "name": "field2",
       "datatype": "String"
   }
]

}

現在我必須動態創建一個表，我正在考慮在 Postgres 中編寫一個函式來做到這一點。因此，該表將命名為 test，其中包含 2 個欄位，一個為字元串，另一個為 bigint。

我可以通過如下選擇來獲取表名：

select (metadata->'tablename') from public.json_metadata;

但是，我很難讓所有嵌套的列名形成一個 create table 語句。

1-你將如何去做，任何內置的 Postgres 函式來提取它。

2- Postgres 函式是解決此問題的最佳方法，還是我應該用 python（我必須學習 Python）或 shell 腳本編寫它。

列數不會是固定的，不同的 json 文件會有不同的列數。

-- Read json fields and create the create table statement
       -- Check if primarykey is null
       if v_pkey::text = 'null'
       then
           v_create_stmt := 'SELECT format(''CREATE TABLE IF NOT EXISTS %s (%s);'', y.tname2, y.cols)
                   FROM
                       (select s.tname1 as tname2,
                       string_agg(common.f_remove_non_alphanumerics(lower((s.details ->> ''dbname'')::text), '''') || '' '' || lower((s.details->>''datatype'')::text) || '' '' || case when (s.details->>''isRequired'') = ''true''
                       then ''not null''
                       else ''null''
                       end, '', '') AS cols
                       from (
                           select schema ||''.''|| tname as tname1, json_array_elements(colname) as details
                           from common.json_metadata
                       ) s
                       group by s.tname1
                   ) y;';
       else
           v_create_stmt := 'SELECT format(''CREATE TABLE IF NOT EXISTS %s (%s, PRIMARY KEY (%s));'', y.tname2, y.cols, y.pkey)
                 FROM
                     (select s.tname1 as tname2,
                     string_agg(common.f_remove_non_alphanumerics(lower((s.details ->> ''dbname'')::text), '''') || '' '' || lower((s.details->>''datatype'')::text) || '' '' || case when (s.details->>''isRequired'') = ''true''
                     then ''not null''
                     else ''null''
                     end, '', '') AS cols,
                     string_agg( case when (s.pkey_dtl ->> ''namepk'')::text is not null
                     then common.f_remove_non_alphanumerics((s.pkey_dtl ->> ''namepk'')::text, '''')
                     else null
                     end, '', '') AS pkey
                     from (
                         select schema ||''.''|| tname as tname1, json_array_elements(colname) as details, json_array_elements(primarykey) as pkey_dtl
                         from common.json_metadata
                     ) s
                     group by s.tname1
                 ) y;';
       end if;

為此，您需要動態 SQL，這會帶來 SQL 注入的危險。

但是，如果操作得當，這對 SQLi 來說是安全的：

DO
$do$
BEGIN

EXECUTE (
  SELECT format('CREATE TABLE %I(%s)', metadata->>'tablename', c.cols)
  FROM   public.json_metadata m
  CROSS  JOIN LATERAL (
     SELECT string_agg(quote_ident(col->>'name')
                       || ' ' ||  (col->>'datatype')::regtype, ', ') AS cols
     FROM   json_array_elements(metadata->'columns') col
     ) c
  );
END
$do$;

表名和列名被視為區分大小寫。（您可能需要小寫字母。）類型名稱被視為不區分大小寫，並且任何有效的類型名稱都有效。

當然，這會為您顯示的不存在的數據類型引發異常。 String嘗試使用text。

注意使用format()、對象標識符類型regclass、 、子查詢quote_ident()中的列聚合以及執行動態 SQL的命令。LATERALDO

有關的：

• 如何進行這種二進制外觀分析
• Postgres 函式中的 SQL 注入與準備好的查詢
• 表名作為 PostgreSQL 函式參數

引用自：https://dba.stackexchange.com/questions/206082