將 PL/pgSQL 中動態查詢的輸出寫入 CSV 文件

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

我在 plpgsql 中寫了匿名塊。現在，我不想使用raise notice我想以 CSV 格式編寫此輸出。我試過COPY了，但它接受參數作為查詢。

有一個非常簡單的解決方案。

SQL 程式碼

去除大部分雜物後，歸結為：

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$

• 簡化format()通話。多次重複使用相同的參數。在此處閱讀文件。
• 無需cnames將customer表中的數據填充到數組中進行處理。只需使用FOR循環。
• 表別名和USING子句等標準技術有助於簡化 SQL 程式碼。

外殼函式

將其添加回您的shell 函式：

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

瞧。

• 將整個查詢用單引號括起來以表示 shell 的範圍。與雙引號不同，shell 不會嘗試任何替換，並且字元串按原樣傳遞（特殊字元也$失去其特殊含義）。
• 切換到 plpgsql 程式碼中的美元引用以與外殼中的外部單引號一起使用（或正確轉義所有單引號）。
• customer_schema為了快速解決，我在範例中命名了輸出文件。您可能需要將其清理為合法的文件名語法。

你正在做的可能不是正確的方法。你想要的是…

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

癥結可能是你的循環。您可以輕鬆擺脫這個問題：有一個名為 unnest 的函式，它可以將數組轉換為一個表，然後可以連接或其他任何東西（可能通過 LATERAL 連接）。

你的方法是錯誤的。如果你真的想要這個循環等等，你需要一個集合返回函式並在 COPY 中使用它。

引用自：https://dba.stackexchange.com/questions/93877