How to execute pgsql2shp from batch file

Question

I am trying to execute pgsql2shp for a remote PostgreSQL db using batch file using the below code

for /f %%i in ('psql -U ihadmin -d ih_gis_production -h <xxxxx> -c "select f_table_name from geometry_columns where f_table_schema='mls_dataset';"') do (
set file_name=D:/Processing/DB_Dump/airdrie/mls_datatset/%%i
set table_name=%%i
pgsql2shp -h <xxxxx> -f %file_name% -u ihadmin -P xxxxx ih_gis_production mls_dataset.%table_name%
)

This will get table names from from the above select query and loop and export those tables to shapefile. When I run this from batch file, I am seeing pgsql2shp help options in command prompt. Any correct way to call pgsql2shp via batch?

Answer 1

Variable assignation/retrieval within a loop should be done differently, as explained here.

As it is, %table_name% is always null. You must use setlocal enabledelayedexpansion and retreive it with !table_name!

rem ENABLEDELAYEDEXPANSION : allow creating variables in FOR loop
setlocal enabledelayedexpansion 
for /f %%i in ('psql -U ihadmin -d ih_gis_production -h <xxxxx> -c "select f_table_name from geometry_columns where f_table_schema='mls_dataset';"') do (
 set file_name=D:/Processing/DB_Dump/airdrie/mls_datatset/%%i
 set table_name=%%i
 echo !table_name!
 pgsql2shp -h <xxxxx> -f !file_name! -u ihadmin -P xxxxx ih_gis_production mls_dataset.!table_name!
)