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 <barw...@gmail.com> wrote: > 2020年12月26日(土) 20:19 Markur Sens <markurs...@gmail.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 >
