CODEWITHSUNDEEP
batch-filecmdpg-dump
Stephan
Stephan

Reputation: 43083

How to escape this command in a batch file?

I want to generate dynamically a set of switches for PG_DUMP like below:

--table=mySchema.foo --table=mySchema.bar ...

However, I want to restrict those switches to views only. The views names don't follow a pattern. They all reside in a single schema called mySchema.

Here is the batch file script I wrote:

@echo off

set PARAM_HOTE=localhost

set PARAM_PORT=5435

set PSQL="C:\Program Files\PostgreSQL\9.4\bin\psql.exe"

set SQL_QUERY=^
select string_agg( '--table=' || quote_ident(nspname) || '.' || quote_ident(relname), ' ' )^
  from (^
      select *^
        from pg_class^
        join pg_namespace on pg_namespace.oid = pg_class.relnamespace^
       where relkind = 'v'^
         and nspname = 'mySchema'^
    order by relname ASC^
  ) infos_vues^
;

for /f %%i in ('"%PSQL%" --quiet --tuples-only --host %PARAM_HOTE% --port %PARAM_PORT% --username "rec" -c "%SQL_QUERY%" db') do set PG_DUMP_SWITCHES_FOR_VIEWS_ONLY=%%i

:: Call PG_DUMP...

When I run it, I am getting the following error:

'"C:\Program Files\PostgreSQL\9.4\bin\psql.exe"" -c "select' is not recognized as an internal or external command, operable program or batch file.

Upvotes: 0

Views: 95

Answers (1)

Stephan
Stephan

Reputation: 43083

Here is how I solved my issue:

@echo off

set PARAM_HOTE=localhost

set PARAM_PORT=5435

set PSQL="C:\Program Files\PostgreSQL\9.2\bin\psql.exe"

set SQL_LISTE_VUES=^
select string_agg( concat('--table=' , quote_ident(nspname) , '.' , quote_ident(relname)), ' ' )^
  from (^
      select *^
        from pg_class^
        join pg_namespace on pg_namespace.oid = pg_class.relnamespace^
       where relkind = 'v'^
         and nspname = 'rec'^
    order by relname ASC^
  ) infos_vues^
;

for /f  "usebackq delims=" %%i in (`%%PSQL%% --quiet --tuples-only --host %PARAM_HOTE% --port %PARAM_PORT% --username "rec" -c "%SQL_LISTE_VUES%" REC`) do set LISTE_VUES=%%i
echo %LISTE_VUES%
  • I rewrote my query by replacing || with the concat function
  • I used back ticks
  • I escaped % with %% in the for command

Upvotes: 1

Related Questions