The following bug has been logged on the website:
Bug reference: 14108
Logged by: Neeraj kumar
Email address: neeraj.chaurasia@wipro.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.chaurasia@wipro.com