On 9/12/20 11:11 AM, Magnus Hagander wrote: > > > On Sat, Sep 12, 2020 at 5:06 PM Tom Lane <t...@sss.pgh.pa.us > <mailto:t...@sss.pgh.pa.us>> wrote: > > Magnus Hagander <mag...@hagander.net <mailto:mag...@hagander.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. > >
A more robust recipe would use "jq -c" as the filter. And it's available on Windows via chocolatey. I don't have a strong opinion on the suggested facility. Presumably you can construct a function that runs COPY into a temp table and then returns the results. But maybe that's more work than we should require users to perform. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
