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

On Fri, Apr 22, 2016 at 12:07 AM, <neeraj.chaurasia@wipro.com> wrote:

> 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 cop=
y
> and remove ; from end to avoid parsing error.
>
> But while executing my sql containing \copy command I supply some variabl=
e
> 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 we=
ll
> 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=3DMEC.subscriber_decimal
>  WHERE
>  MES.AIRTIME_EXPIRY_DT <=3D  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=3D'/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=3D20160421-v
> v1=3D\'/mis_purging/data_sth/SCORP_DLY_GRACE_BASE_20160419.csv\'
> -v v3=3D20160421
>

=E2=80=8BFirst, this is working as designed - I do agree that a better desi=
gn would
be nice but alas you have to make do with what you are given.

=E2=80=8BI'll presume that the copy command is all on one line as opposed t=
o 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 [...]

=E2=80=8BIn your situation you are fortunate since the location where you w=
ant to
=E2=80=8Bplace the variable is one that can be moved into the aforementione=
d 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=3D/tmp/file
psql <<SQL
\copy (SELECT * FROM tbl) TO '$FILEPATH'
SQL

=E2=80=8BThe 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.=E2=80=8B

=E2=80=8BDavid J.