Re: Function to execute a program

On Sat, Sep 12, 2020 at 5:06 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > Would it make sense to have a pg_execute_program() that corresponds to
> COPY
> > FROM PROGRAM? This would obviously have the same permissions restrictions
> > as COPY FROM PROGRAM.
> > The usecase would be to for example execute a command that returns json
> > format output, which could then be parsed and processed as part of a
> query.
> > Today, COPY FROM PROGRAM cannot do this, as we can't read a multiline
> json
> > value with COPY.
>
> copy ... from program 'random_json_producer | tr "\n\t" " "';
>
> I don't necessarily object to providing such a function to make it
> easier, but it's not the case that you can't have the functionality
> today.
>

"tr" is not typically available on Windows, for one :)

But yes, assuming tr is available, it is true that you can. (And you can
perhaps find something else to pipe it through on Windows). Of course, you
still can't use it in a query, since COPY can only target a table :)
Independently of something like this it would be nice to be able to target
say a CTE with COPY, but that's kan entirely different topic.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>