Re: Postgres read jsonb content from stdin

Hello,

Hadn't really thought of using a foreign table up at this point...
thanks for that.

Will the first solution handle formatting issues (e.g. single quotes)
gracefully?

I think I'd tried it in the past and it didn't work.

PD: I have such a script that handle's the intricacies but it'd still emit
to stdout. (hence the curl simplified in the example)

On Sat, Dec 26, 2020 at 2:40 PM Ian Lawrence Barwick <barwick(at)gmail(dot)com>
wrote:

> 2020年12月26日(土) 20:19 Markur Sens <markursens(at)gmail(dot)com>:
> >
> > 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`
> <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
>