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

From: neeraj(dot)chaurasia(at)wipro(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14108: \Copy Command does not takes varibales supplied using -v
Date: 2016-04-22 07:07:05
Message-ID: 20160422070705.22934.49259@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

regards,
Neeraj Kumar
ph: +91-8587039823
mail: neeraj(dot)chaurasia(at)wipro(dot)com

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2016-04-22 08:56:49 Re: BUG #14107: Major query planner bug regarding subqueries and indices
Previous Message Mathias Kunter 2016-04-22 05:49:40 Re: BUG #14107: Major query planner bug regarding subqueries and indices