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@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     Magnus Hagander <magnus@hagander.net <mailto:magnus@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