帶索引的動態 CREATE TABLE AS

我正在將我們的主數據庫從 SQL Server 遷移到 PostgreSQL（同時在此過程中學習它）。

我需要移動的一件事是一堆儲存過程，它們生成具有動態列名的表。我開始工作沒有問題。問題是其中一些表後來被用來抓取數據以在另一個儲存過程中生成另一個表。這些後續過程可能需要很長時間才能生成，有時需要 3-5 分鐘。雖然這些查詢非常複雜，但我懷疑這是因為動態創建的表不包含索引，甚至不包含主鍵。

下面是最簡單的過程的範常式式碼。有人可以告訴我如何在該過程中將主鍵添加到第一列（material），然後將額外的索引添加到第 6 列（total_open）

CREATE OR REPLACE PROCEDURE sap_data.sp_ssa_po_final()
LANGUAGE plpgsql
AS $procedure$
DECLARE
   month_next_5 varchar(3) := to_char(NOW() + interval '5 month', 'mon');
   month_next_4 varchar(3) := to_char(NOW() + interval '4 month', 'mon');
   month_next_3 varchar(3) := to_char(NOW() + interval '3 month', 'mon');
   month_next_2 varchar(3) := to_char(NOW() + interval '2 month', 'mon');
   month_next_1 varchar(3) := to_char(NOW() + interval '1 month', 'mon');
   month_now varchar(3) := to_char(NOW(), 'mon');
   month_prev_1 varchar(3) := to_char(NOW() - interval '1 month', 'mon');
   month_prev_2 varchar(3) := to_char(NOW() - interval '2 month', 'mon');
   month_prev_3 varchar(3) := to_char(NOW() - interval '3 month', 'mon');
   month_prev_4 varchar(3) := to_char(NOW() - interval '4 month', 'mon');
   month_prev_5 varchar(3) := to_char(NOW() - interval '5 month', 'mon');
   month_prev_6 varchar(3) := to_char(NOW() - interval '6 month', 'mon');
   sql_string varchar(3000) := '';

BEGIN


sql_string := 'CREATE TABLE sap_ssa_po_final AS SELECT 
mat_no AS "material", 
material_descr AS "material_description", 
('||month_now||'+'||month_prev_1||'+'||month_prev_2||'+'||month_prev_3||'+'||month_prev_4||'+'||month_prev_5||'+'||month_prev_6||') as "open_till_'||month_now||'",
'||month_next_1||' AS "sum_'||month_next_1||'_qty",
'||month_next_2||' AS "sum_'||month_next_2||'_qty",
('||month_now||'+'||month_prev_1||'+'||month_prev_2||'+'||month_prev_3||'+'||month_prev_4||'+'||month_prev_5||'+'||month_prev_6||'+'||month_next_1||'+'||month_next_2||') as "total_open",
'||month_next_3||' AS "sum_'||month_next_3||'_qty",
'||month_next_4||' AS "sum_'||month_next_4||'_qty",
'||month_next_5||' AS "sum_'||month_next_5||'_qty",
NOW() as created_date 
FROM v_ssa_po_summarised';

execute sql_string;
end;
$procedure$
;

我確實嘗試用Google搜尋答案，但沒有找到答案。:(

我被你程式碼中的噪音觸發了。考慮重寫。

在此期間，我還回答了您的問題。

CREATE OR REPLACE PROCEDURE sap_data.sp_ssa_po_final()
 LANGUAGE plpgsql AS
$proc$
DECLARE
  mon text[];
  sql_string text;
BEGIN
  -- prepare array with subscripts -6 to +5, and current month at index 0
  SELECT INTO mon
        ('[-6:5]={' || string_agg(to_char(m, 'mon'), ',') || '}')::text[]
  FROM   generate_series(LOCALTIMESTAMP - interval '6 mon'
                       , LOCALTIMESTAMP + interval '5 mon'
                       , interval '1 mon') m;
                       
  sql_string :=
     'CREATE TABLE public.sap_ssa_po_final AS SELECT'
  || concat_ws(E'\n   , ' 
        , E'\n     mat_no AS material'
        , 'material_descr AS material_description'
        , '(' || concat_ws('+', mon[0], mon[-1], mon[-2], mon[-3], mon[-4], mon[-5], mon[-6]) || ') AS open_till_' || mon[0]
        , mon[1] || ' AS sum_' || mon[1] || '_qty'
        , mon[2] || ' AS sum_' || mon[2] || '_qty'
        , '(' || concat_ws('+', mon[0], mon[-1], mon[-2], mon[-3], mon[-4], mon[-5], mon[-6], mon[1], mon[2]) || ') AS total_open'
        , mon[3] || ' AS sum_' || mon[3] || '_qty'
        , mon[4] || ' AS sum_' || mon[4] || '_qty'
        , mon[5] || ' AS sum_' || mon[5] || '_qty'
        , 'now() AS created_date'
        )
  || E'\nFROM v_ssa_po_summarised;'


  -- RAISE NOTICE '%', mon;
  -- RAISE NOTICE '%', sql_string;
  EXECUTE sql_string;

  -- These can be static. Spell out the schema to be sure!
  ALTER TABLE public.sap_ssa_po_final ADD PRIMARY KEY(material);
  CREATE INDEX ON public.sap_ssa_po_final (total_open);
END
$proc$;

在創建新表之後，可以使用靜態程式碼創建主鍵和索引。PL/pgSQL 計劃並執行一個又一個語句。（在普通的 SQL 函式中不可能做到這一點，整個函式體都被一次解析。但動態 SQL 無論如何都需要過程語言。）請參閱：

• PostgreSQL 函式中語言 sql 和語言 plpgsql 的區別

但我建議拼出架構名稱以避免與search_path. 我用過public。（您的動態CREATE TABLE語句還沒有架構。）適應您的情況。也許臨時架構pg_temp是您的選擇？看：

• search_path 如何影響標識符解析和“目前模式”

mon我在下標 0 處生成目前月份的月份數組。這可以大大簡化。關於非標準數組下標：

• 規範化數組下標，使其從 1 開始

注意concat_ws(). 看：

• 合併兩列並添加到一個新列中

我與之合作，LOCALTIMESTAMP而不是now()明確本地時區將以任何一種方式使用——這對極端情況有所不同。使用generate_series(). 看：

• 在 PostgreSQL 中生成兩個日期之間的時間序列

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