[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    [BUGS] BUG #14108: \Copy Command does not takes varibales supplied using -v
From:       neeraj.chaurasia () wipro ! com
Date:       2016-04-22 7:07:05
Message-ID: 20160422070705.22934.49259 () wrigleys ! postgresql ! org
[Download RAW message or body]

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




-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[prev in list] [next in list] [prev in thread] [next in thread] 

Configure | About | News | Add a list | Sponsored by KoreLogic