Re: Extending COPY TO

Hi all,
thanks for all your answers, I see your point. And I also understand the argument according to which there always be some other use case to satisfy. However your suggestion to use COPY TO sql TO PROGRAM doesn’t seem to me to fit well the use case I have in mind.

Imagine you access PG from an application written in the language X using a driver library, both your application and your PG instance run on two different hosts. Now using COPY TO sql PROGRAM the output file ends up on the PG host filesystem, while using COPY TO sql STDOUT and passing a file descriptor to PG via the driver library the output file ends up on the application hosts, which is much more convenient from the application point of view.

Sure you can always fix this setting up some kind of shared filesystem, but this is just the first of the issues I could think of. What about the potential I/O errors that could happen while opening/writing the output file? I should replicate them back from the PG host to the application layer, and this is something I’m pretty sure no one wants to go down.

So adding such a feature to PG itself seems to me still the best trade off between complexity and convenience. However, if you are strongly against it, or see a better way to get around this problem, please let me know. As I wrote before, despite being an heavy PG user, it’s my first time on the hackers ML and I don’t want to seem disrespectful of the community.

Thanks,
Andrea

On 23 Sep 2014, at 08:56, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Andrea,
>
> * Andrea Riciputi (andrea(dot)riciputi(at)gmail(dot)com) wrote:
>> My idea was to extend the COPY TO command to accept an EOL option as it already does with the DELIMITER option. To keep it simple we can limit the EOL choice to CR, LF or CRLF to avoid unusual output, and also keep the current behaviour when no EOL option is given. I was also wondering if an EOL option could be useful also for the text output format or not.
>
> Have you considered using COPY TO's 'PROGRAM' option to simply pipe the
> output through unix2dos..?
>
>> I spent the weekend reading the COPY command source code and its grammar definition and I think I can patch it by myself, submit the patch here and wait for your review. However before starting this in my spare time I wanted to know if you, as the PG hackers community, would be against a similar proposal for any reason, and if so why.
>
> I'm not particularly against it, though if it can be solved with the
> existing 'PROGRAM' capability then it may not make sense to complicate
> the COPY code further.
>
> Thanks!
>
> Stephen