I'm creating a function that gets two arrays, one of integers and one of strings then creates a materialized view, naming it with a concatenation of these parameters. In this way:
CREATE OR REPLACE FUNCTION reports_mt_views(
accounts integer[],
namesa text[])
RETURNS void AS
$BODY$
DECLARE
i integer;
BEGIN
FOREACH i IN ARRAY accounts
LOOP
EXECUTE 'CREATE MATERIALIZED VIEW pps.order_reports_materialized_' || accounts[i] || '_' || namesa[i] || ' AS
...
However when I launch the function as follows:
SELECT reports_mt_views(ARRAY [1,9,504231,505674], '{''a'',''b'',''c'',''d''}');
I get the following error:
ERROR: syntax error at or near "'a'"
LINE 1: ...TE MATERIALIZED VIEW pps.order_reports_materialized_1_'a'
It happens because the simple quotes that I'm sending. Otherwise I can not send the array of strings, if I do so:
SELECT reports_mt_views(ARRAY [1,9,504231,505674], {'a','b','c','d'}, 3);
ERROR: syntax error at or near "{"
LINE 1: ...T pps.reports_mt_views(ARRAY [1,9,504231,505674], {'a','b','...
^
I can not send and read the array of strings to generate the name of the materialized view.