1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.