Re: Postgres read jsonb content from stdin

[Ian Lawrence Barwick <barwick@xxxxxxxxx>]

2020年12月26日(土) 20:19 Markur Sens <markursens@xxxxxxxxx>:
>
> Hello,
>
> I'm trying to build a few data pipelines with Unix tools but can't figure out how to insert in a slurp mode (e.g. not COPY line by line) content inside a variable.
>
> Consider the following script (using a heredoc)
>
> json_url="https://....file.json";
> local_file="/tmp/a.json"
>
> curl -s -m 10 -A 'Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0' \
>     --max-redirs 0 -o ${local_file} ${json_url}
>
> psql "$PG_URI" -qAt <<SQL
> create table if not exists (data jsonb);
>
> insert into my_table(data) values (pg_read_file('${local_file}')::jsonb)
> on conflict do nothing;
> SQL
>
> The question is, how can I achieve the same result, without having to hit the disk due. to the temporary file.
> I tried running by using pg_read_file('/dev/stdin')::jsonb

It can be done like this:

    $ curl http://localhost/json.txt
    {"bar": "baz", "balance": 7.77, "active": false}

    $ psql -v jsonval="`curl -s http://localhost/json.txt`"; -d
'host=localhost dbname=postgres user=postgres' <<SQL
    INSERT INTO json_test values(:'jsonval')
    SQL

    INSERT 0 1
    Time: 0.374 ms

though TBH if I were doing that on a regular basis, I'd do it via a script which
could cope with errors retrieving the remote file, etc.

If the data source (URL) is constant, you could try something along these lines
with file_fdw:

    CREATE EXTENSION file_fdw;

    CREATE SERVER json_curl FOREIGN DATA WRAPPER file_fdw;
    CREATE FOREIGN TABLE json_src (
      json_data jsonb
    )
    SERVER json_curl
    OPTIONS (
      PROGRAM 'curl -s http://localhost/json.txt'
    );


Better alternatives may be available.


Regards

Ian Barwick



-- 
EnterpriseDB: https://www.enterprisedb.com