0
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.

2

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.

0

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.

  • The problem there that to OP wants to use dynamic schema specification. – dezso Feb 26 '15 at 10:15
  • copy can only write output of query.I want to pass variable along with it(customer_schema). – user3526905 Feb 26 '15 at 10:15
  • 2
    as i said: in this case put things into a procedure returning "setof record" and use copy. you can do anything inside a procedure. – Hans-Jürgen Schönig Feb 26 '15 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.