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

dynamic-sqlplpgsqlpostgresqlscripting

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.

Best Answer

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.

Related Solutions

Postgresql – Save output from multiple SQL SELECT commands to a file

Basic function to return rows from multiple SELECT statements:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS TABLE(a_id integer, txt text) AS
$func$
BEGIN

RETURN QUERY SELECT a.a_id, a.txt FROM tbl_a a;

RETURN QUERY SELECT b.a_id, b.txt FROM tbl_b b;

END
$func$ LANGUAGE plpgsql;

You could do the same with UNION ALL.

Simple & quick demo to output from tables with different structure:

CREATE OR REPLACE FUNCTION f_get_rows()
  RETURNS SETOF text AS
$func$
BEGIN

RETURN QUERY SELECT t::text FROM tbl_a t;

RETURN QUERY SELECT t::text FROM tbl_c t;

END
$func$ LANGUAGE plpgsql;

You can cast any type to text in PostgreSQL, even the composite type of a table row ...

Call:

SELECT * FROM f_get_rows()

To write this to a file (local to the server):

COPY (SELECT * FROM f_get_rows()) TO '/path/to/my/file.csv';

To get you started read these chapters in the manual:

For a more specific answer you need to write a more specific question.

Dynamic SQL vs. SQLCMD – User-Shared Queries Comparison

Just to get these out of the way:

Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity to replace ' with '', which may or may not work depending on where the variables are being used).
There are pros and cons to each approach:
- SQL scripts in SSMS can be easily edited (which is great if that is a requirement) and working with results is easier than with output from SQLCMD. On the down-side, the user is in an IDE so it is easy to mess up the SQL, and the IDE makes it easy to make a great variety of change without knowing the SQL to do it.
- Running scripts via SQLCMD.EXE does not allow the user to easily make changes (without editing the script in an editor and then saving it first). This is great if the users aren't supposed to be changing the scripts. This method also allows for logging each execution of it. On the down-side, if there is a need to routinely edit the scripts then it would be quite cumbersome. Or, if users need to scan through 100k rows of a result set and/or copy those results to Excel or something, then that is also difficult in this approach.

If your support folks aren't doing ad hoc queries and are just filling out those variables, then they don't need to be in SSMS where they can edit those scripts and make unwanted changes.

I would create CMD scripts to prompt the user for the desired variable values and then call SQLCMD.EXE with those values. The CMD script could even log the execution to a file, complete with time stamp and variable values submitted.

Create one CMD script per SQL script and place in a networked shared folder. A user double-clicks on the CMD script and it just works.

Here is an example that:

prompts the user for the server name (no error checking on that yet)
prompts the user for the database name
- if left blank, it will list the databases on the specified server and prompted again
- if the database name is invalid, the user will be prompted again
prompts the user for the OrderIDsSeparatedByCommas
- if blank, prompts the user again
runs the SQL script, passing in the value of %OrderIDsSeparatedByCommas% as the SQLCMD variable $(OrderIDsSeparatedByCommas)
logs the execution date, time, ServerName, DatabaseName, and OrderIDsSeparatedByCommas to a log file named for the Windows Login running the script (this way, if the log directory is network and there are multiple people using this, there won't be any write contention on the log file like there might be if the USERNAME were to be logged in the file per entry)
- if the log file directory does not exists, it will be created

Test SQL script (named: FixProblemX.sql):

SELECT  *
FROM    sys.objects
WHERE   [schema_id] IN ($(OrderIdsSeparatedByCommas));

CMD script (named: FixProblemX.cmd):

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

SET ScriptLogPath=\\server\share\RunSqlCmdScripts\LogFiles

CLS

SET /P ScriptServerName=Please enter in a Server Name (leave blank to exit): 

IF "%ScriptServerName%" == "" GOTO :ThisIsTheEnd

REM echo %ScriptServerName%

:RequestDatabaseName
ECHO.
SET /P ScriptDatabaseName=Please enter in a Database Name (leave blank to list DBs on %ScriptServerName%): 

IF "%ScriptDatabaseName%" == "" GOTO :GetDatabaseNames

SQLCMD -b -E -W -h-1 -r0 -S %ScriptServerName% -Q "SET NOCOUNT ON; IF (NOT EXISTS(SELECT [name] FROM sys.databases WHERE [name] = N'%ScriptDatabaseName%')) RAISERROR('Invalid DB name!', 16, 1);" 2> nul

IF !ERRORLEVEL! GTR 0 (
    ECHO.
    ECHO That Database Name is invalid. Please try again.

    SET ScriptDatabaseName=
    GOTO :RequestDatabaseName
)

:RequestOrderIDs
ECHO.
SET /P OrderIdsSeparatedByCommas=Please enter in the OrderIDs (separate multiple IDs with commas): 

IF "%OrderIdsSeparatedByCommas%" == "" (

    ECHO.
    ECHO Don't play me like that. You gots ta enter in at least ONE lousy OrderID, right??
    GOTO :RequestOrderIDs
)


REM Finally run SQLCMD!!
SQLCMD -E -W -S %ScriptServerName% -d %ScriptDatabaseName% -i FixProblemX.sql -v OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%

REM Log this execution
SET ScriptLogFile=%ScriptLogPath%\%~n0_%USERNAME%.log
REM echo %ScriptLogFile%

IF NOT EXIST %ScriptLogPath% MKDIR %ScriptLogPath%

ECHO %DATE% %TIME% ServerName=%ScriptServerName%    DatabaseName=[%ScriptDatabaseName%] OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%   >> %ScriptLogFile%

GOTO :ThisIsTheEnd

:GetDatabaseNames
ECHO.
SQLCMD -E -W -h-1 -S %ScriptServerName% -Q "SET NOCOUNT ON; SELECT [name] FROM sys.databases ORDER BY [name];"
ECHO.
GOTO :RequestDatabaseName

:ThisIsTheEnd
PAUSE

Be sure to edit the ScriptLogPath variable towards the top of the script.

Also, the SQL scripts (specified by the -i command-line switch for SQLCMD.EXE) might benefit from having a fully-qualified path, but not entirely sure.