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

Question

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.

• Erwin Brandstetter: I tried 'COPY' but it accepts argument as a query I fail to understand that. Can you clarify? COPY should work.
• Erwin Brandstetter: So do you have your answer?

Answer 1

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.

Answer 2

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.

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