Dynamic CREATE TABLE AS with indexes

Question

I'm in the process of migrating our main db from SQL Server to PostgreSQL (while learning it in the process).

One of the things I need to move are a bunch of stored procedures, that generate tables with dynamic column names. That I got working with no problems. Thing is that some of those tables are later used to grab data to generate yet another table inside another stored procedure. Those subsequent procedures can take a long time to generate, 3-5 minutes sometime. While those are quite complex queries, I suspect this is due to the fact that the dynamically created tables contain no indexes, not even a primary key.

Below is a sample code for the least complex procedure. Could someone tell me how can I, within that procedure, add a primary key to the first column (material) and then add extra index to the 6th column (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$
;

I did try to google for answers but came up short. :(

• Akina: Add according CREATE INDEX after execute sql_string;.
• user1822: alter table add primary key(...) and create index on ... (...)
• Topper81: Thanks @Akina - I was thinking about doing just that, but thought maybe there is a way to included in the create as statement that I was not aware of

Answer 1

I got triggered by the noise in your code. Consider the rewrite.

While being at it, I also answered your question.

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

Primary key and index can just be created with static code, after the new table has been created. PL/pgSQL plans and executes one statement after the other. (The same would not be possible in a plain SQL function, where the whole function body is parsed at once. But dynamic SQL requires a procedural language anyway.) See:

• Difference between language sql and language plpgsql in PostgreSQL functions

But I would advise to spell out the schema name to avoid mishaps (or even malicious attempts) with the search_path. I used public. (Your dynamic CREATE TABLE statement had no schema, yet.) Adapt to your case. Maybe the temporary schema pg_temp is an option for you? See:

• How does the search_path influence identifier resolution and the "current schema"

I generate the array of months mon with the current month at subscript 0. This allows to simplify quite a bit. About non-standard array subscripts:

• Normalize array subscripts so they start with 1

Note the strategic use of concat_ws(). See:

• Combine two columns and add into one new column

I work with LOCALTIMESTAMP instead of now() to make clear that the local time zone will be used either way - which makes a difference for corner cases. It's also slightly more efficient with generate_series(). See:

• Generating time series between two dates in PostgreSQL