Write output from dynamic queries in PL/pgSQL to CSV file

PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD " 
        DO $$
        DECLARE
           srowdata record;
           customer_list varchar[];
           customer_schema varchar(100);
           portal_row a.portal%ROWTYPE;
           var1 varchar(100);
           temp varchar(100);
        BEGIN
           customer_list=ARRAY(select cname from customer);

            FOREACH customer_schema IN ARRAY customer_list LOOP 

               EXECUTE format('select %s.portal.*,%s.p_fb_config.*,%s.p_gplus_config.*,%s.p_linkd_config.*,%s.p_localum_config.*,
                   %s.p_sms_config.*,%s.p_twt_config.*,%s.p_webform_config.*,%s.p_wechat_config.*,%s.p_clickthrough_config.*,%s.splash.*
               from %s.portal left outer join %s.p_fb_config on %s.portal.pid = %s.p_fb_config.pid left outer join %s.p_gplus_config 
               on %s.portal.pid = %s.p_gplus_config.pid left outer join %s.p_linkd_config on %s.portal.pid = %s.p_linkd_config.pid left 
               outer join %s.p_localum_config on %s.portal.pid = %s.p_localum_config.pid left outer join %s.p_sms_config on 
               %s.portal.pid = %s.p_sms_config.pid  left outer join %s.p_twt_config on %s.portal.pid = %s.p_twt_config.pid left outer join 
               %s.p_webform_config on %s.portal.pid = %s.p_webform_config.pid left outer join %s.p_wechat_config on 
               %s.portal.pid = %s.p_wechat_config.pid  left outer join %s.p_clickthrough_config on 
               %s.portal.pid = %s.p_clickthrough_config.pid left outer join %s.splash on %s.portal.pid=%s.splash.pid;', customer_schema, 
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema, 
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema) INTO srowdata;

                   raise notice ' %: %', customer_schema,srowdata;
            END LOOP;
        END; 
        $$";
}

abc

I have written anonymous block in plpgsql. Now, instead of using raise notice I want to write this output in CSV format. I tried COPY but it accepts argument as a query.

There is a massively simpler solution.

SQL code

After removing much of the cruft, it boils down to this:

DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP 
      EXECUTE format('COPY (
         SELECT p.*, t1.*, t2.* -- etc. Or just: *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         -- etc.
         ) TO $$/absolute/path/to/my/%1$I_file.csv$$;'
       , customer_schema);
   END LOOP;
 END 
$do$

• Simplify the format() call. Reuse the same parameter many times. Read the documentation here.

• No need to stuff the cnames from your customer table into an array for processing. Just use a FOR loop.

• Standard techniques like table aliases and the USING clause help to simplify SQL code.

Shell function

Adding that back into your shell function:

PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD_TP '
DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP 
      EXECUTE format($f$COPY (
         SELECT *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         ) TO $p$/absolute/path/to/my/%1$s_file.csv$p$;$f$
       , customer_schema);
   END LOOP;
 END 
$do$';
}

abc

Voilá.

• Enclose the whole query in single quotes for the scope of the shell. Unlike with double quotes the shell does not try any substitutions and the string is passed as is (special characters like $ lose their special meaning, too).

• Switch to dollar-quoting in the plpgsql code to go along with the outer single quotes in the shell (or escape all single quotes properly).

• For a quick solution I named the output files after customer_schema in the example. You may need to sanitize it to be a legal for filename syntax.

what you are doing is maybe not the right approach.
what you want is …

COPY (SELECT ...) TO 'some_file_name' CSV;

the crux might be your loop. you can get rid around that one easy: there is a function called unnest, which can transform an array into a table which can then be joined or whatever (via a LATERAL join maybe).

you are on the wrong path with your approach.
if you really want this loop and so on, you need a set returning function and use it inside COPY.