Thread: How to give \COPY inside a function
Hi All,
I want to \COPY over COPY command as I am not running as a super/admin user. How to add \COPY command inside a function.
The following statement inside a function throws error.
\COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER
Errors are throwing at “\COPY” and no semi-colon at the end of the line. Please guide me.
Thanks and Regards,
Siva.
::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------
The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.
-----------------------------------------------------------------------------------------------------------------------
On 08/16/11 8:07 AM, Siva Palanisamy wrote: > > I want to \COPY over COPY command as I am not running as a super/admin > user. How to add \COPY command inside a function. > > The following statement inside a function throws error. > > \COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER > > Errors are throwing at “\COPY” and no semi-colon at the end of the > line. Please guide me. > \COPY is a psql metacommand, not a proper SQL command, its translated to a SQL COPY command. you can't use \ commands in functions, so you'll need to recode that as a COPY ... TO STDOUT ...; and recieve the stdout stream and write it to your file (which is what the \COPY command is doing). -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Hi John, I can understand that we can't use \COPY command directly in a function. Could you please brief me your alternative solutionwith a sample piece of code or any useful link? Thanks and Regards, Siva. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce Sent: Tuesday, August 16, 2011 8:44 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to give \COPY inside a function On 08/16/11 8:07 AM, Siva Palanisamy wrote: > > I want to \COPY over COPY command as I am not running as a super/admin > user. How to add \COPY command inside a function. > > The following statement inside a function throws error. > > \COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER > > Errors are throwing at "\COPY" and no semi-colon at the end of the > line. Please guide me. > \COPY is a psql metacommand, not a proper SQL command, its translated to a SQL COPY command. you can't use \ commands in functions, so you'll need to recode that as a COPY ... TO STDOUT ...; and recieve the stdout stream and write it to your file (which is what the \COPY command is doing). -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ::DISCLAIMER:: ----------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. -----------------------------------------------------------------------------------------------------------------------
Hi John, I would like to add that I was not given the super user privilege to perform COPY command. If so, I wouldn't see \COPY commandat all! Could you please guide me briefly on how to use \COPY command for my usage to write the output into a .CSVfile inside a function? Thanks and Regards, Siva. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Siva Palanisamy Sent: Wednesday, August 17, 2011 10:25 AM To: John R Pierce; pgsql-general@postgresql.org Subject: Re: [GENERAL] How to give \COPY inside a function Hi John, I can understand that we can't use \COPY command directly in a function. Could you please brief me your alternative solutionwith a sample piece of code or any useful link? Thanks and Regards, Siva. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce Sent: Tuesday, August 16, 2011 8:44 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to give \COPY inside a function On 08/16/11 8:07 AM, Siva Palanisamy wrote: > > I want to \COPY over COPY command as I am not running as a super/admin > user. How to add \COPY command inside a function. > > The following statement inside a function throws error. > > \COPY xsa.export_table TO 'export.csv' DELIMITERS ',' CSV HEADER > > Errors are throwing at "\COPY" and no semi-colon at the end of the > line. Please guide me. > \COPY is a psql metacommand, not a proper SQL command, its translated to a SQL COPY command. you can't use \ commands in functions, so you'll need to recode that as a COPY ... TO STDOUT ...; and recieve the stdout stream and write it to your file (which is what the \COPY command is doing). -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ::DISCLAIMER:: ----------------------------------------------------------------------------------------------------------------------- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ----------------------------------------------------------------------------------------------------------------------- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 08/16/11 10:03 PM, Siva Palanisamy wrote: > Hi John, > > I would like to add that I was not given the super user privilege to perform COPY command. If so, I wouldn't see \COPYcommand at all! Could you please guide me briefly on how to use \COPY command for my usage to write the output intoa .CSV file inside a function? a postgres function can not write a file, but it can execute a COPY .... TO STDOUT ...; without superuser privilege. your invoking application program would use whatever function in its Postgres binding to read this 'stdout' data (its not really stdout), like in C/C++ with libpq.so, you would use PQgetCopyData http://www.postgresql.org/docs/current/static/libpq-copy.html#LIBPQ-COPY-RECEIVE to receive this data stream, which you would write to a file via your application -- john r pierce N 37, W 122 santa cruz ca mid-left coast