问动态创建表与索引一样
EN

Database Administration用户

提问于 2022-02-18 04:10:51

回答 1查看 1.3K关注 0票数 0

我正在将我们的主db从Server迁移到PostgreSQL (在这个过程中学习它)。

我需要移动的东西之一是一堆存储过程，它们生成具有动态列名的表。我的工作没有任何问题。问题是，其中一些表后来被用来获取数据，以便在另一个存储过程中生成另一个表。这些后续程序可能需要很长时间才能生成，有时需要3-5分钟。虽然这些查询相当复杂，但我怀疑这是因为动态创建的表不包含索引，甚至不包含主键。

下面是最不复杂过程的示例代码。有人能告诉我，在这个过程中，我如何在第一列(material)中添加一个主键，然后向第六列(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$
;

我确实尝试过在谷歌上寻找答案，但结果却很短。:(

postgresql

postgresql-performance

functions

dynamic-sql

plpgsql

云直播特惠9.9元起

针对高并发播放、高并发推流、超低延迟等不同直播场景，提供极速、稳定、专业的一站式云端直播处理服务

回答 1

Database Administration用户

回答已采纳

发布于 2022-02-18 17:43:00

我是被你代码中的噪音触发的。考虑一下重写。

同时，我也回答了你的问题。

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$;