Re: Function to execute a program

On 9/12/20 11:11 AM, Magnus Hagander wrote:
>
>
> On Sat, Sep 12, 2020 at 5:06 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Magnus Hagander <magnus(at)hagander(dot)net <mailto: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.
>
>

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