Re: BUG #14108: \Copy Command does not takes varibales supplied using -v

On Fri, Apr 22, 2016 at 12:07 AM, <neeraj(dot)chaurasia(at)wipro(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14108
> Logged by: Neeraj kumar
> Email address: neeraj(dot)chaurasia(at)wipro(dot)com
> PostgreSQL version: Unsupported/Unknown
> Operating system: linux
> Description:
>
> I have requirement where I need to spool out data from psql table using
> copy
> command.
> As I am a non super-user the system suggested to use \copy instead of copy
> and remove ; from end to avoid parsing error.
>
> But while executing my sql containing \copy command I supply some variable
> to indicate out put file name and date etc.
> The issue face is the variables are not getting initialized here .
> while in case where we used super user and only copy command it worked well
> and took all variables.
> Below is the code and run script, please check if it could be resolved.
>
> Copy script
>
> \COPY (select MES.CIRCLE AS CIRCLE,
> MES.subscriber_decimal AS MSISDN,
> MES.SERVICE_CLASS_ID AS SERVICECLASSCD,
> MES.SERVICE_CLASS_DESC AS SERVICECLASSDESC,
> TO_CHAR(MES.ACTIVATION_DT,'DD-MON-YYYY') AS ACTIVATIONDT ,
> TO_CHAR(MES.AIRTIME_EXPIRY_DT,'DD-MON-YYYY') AS AIRTIMEEXPIRYDT,
> TO_CHAR((TO_DATE(:v2,'YYYYMMDD') - MES.AIRTIME_EXPIRY_DT),'DD-MON-YYYY')
> AS DAYSINGRACE,
> TO_CHAR(MES.SERVICE_EXPIRY_DT,'DD-MON-YYYY') AS SERVICEEXPIRYDT,
> TO_CHAR((TO_DATE(:v2,'YYYYMMDD') - MES.SERVICE_EXPIRY_DT),'DD-MON-YYYY')
> AS
> DAYSTOSERVICEEXPIRY,
> MES.ACCOUNT_BALANCE AS BALANCE,
> TO_CHAR(MEC.LAST_RECHARGE_DT,'DD-MON-YYYY')
> AS LASTRECHARGEDT
> FROM mis_prepaid_south.MIS_ERIC_SUBSCRIBER_INFO MES
> LEFT OUTER JOIN
> mis_prepaid_south.MIS_ERIC_CALLDT_TB MEC
> ON MES.subscriber_decimal=MEC.subscriber_decimal
> WHERE
> MES.AIRTIME_EXPIRY_DT <= to_date(:v2, 'YYYYMMDD')-1
> GROUP BY 1,2,3,4,5,6,7,8,9,10,11)
> TO STDOUT WITH CSV HEADER
>
>
> Error :
>
> psql: warning: extra command-line argument
> "v1='/mis_purging/data_sth/SCORP_DLY_GRACE_BASE_20160419.csv'" ignored
> psql:/export/home/mis_ftp/mis/sql/SCORP_DLY_GRACE_BASE_nee.sql:1: ERROR:
> syntax error at or near ":"
> LINE 1: ...YYYY' ) AS AIRTIMEEXPIRYDT, TO_CHAR ( ( TO_DATE ( :v2, 'YYYY...
> ^
> psql:/export/home/mis_ftp/mis/sql/SCORP_DLY_GRACE_BASE_nee.sql:1: \copy:
> ERROR: syntax error at or near ":"
> LINE 1: ...YYYY' ) AS AIRTIMEEXPIRYDT, TO_CHAR ( ( TO_DATE ( :v2, 'YYYY...
> ^
>
> Run script :
>
> psql -U repsouth -h mdw dwh -f
> /export/home/mis_ftp/mis/sql/SCORP_DLY_GRACE_BASE_nee.sql -t -v
> v2=20160421-v
> v1=\'/mis_purging/data_sth/SCORP_DLY_GRACE_BASE_20160419.csv\'
> -v v3=20160421
>

​First, this is working as designed - I do agree that a better design would
be nice but alas you have to make do with what you are given.

​I'll presume that the copy command is all on one line as opposed to being
wrapped as shown. I'd suggest the following idiom to make that part easier
to read:

CREATE TEMP TABLE tbl AS
SELECT [...];

\copy (SELECT * FROM tbl) to [...]

​In your situation you are fortunate since the location where you want to
​place the variable is one that can be moved into the aforementioned CREATE
TEMP TABLE statement. You've avoided the more problematic location, the
output path, by sending the data to STDOUT.

The only realistic way to substitute into the \copy command is to wrap the
psql script within a bash script here-doc and use bash variable
substitution.

FILEPATH=/tmp/file
psql <<SQL
\copy (SELECT * FROM tbl) TO '$FILEPATH'
SQL

​The downside here is that you must allow escaping within the SQL
script...I find that is not generally a problem but is something to be
aware of - especially if you get unexpected syntax errors.​

​David J.