N
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:
https://stackoverflow.com/a/24771561/939860
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:
https://stackoverflow.com/a/9067777/939860
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:
https://stackoverflow.com/q/12011569/939860
Note the strategic use of https://www.postgresql.org/docs/current/functions-string.html . See:
https://stackoverflow.com/a/12320369/939860
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:
https://stackoverflow.com/a/46499873/939860
