I have a function based this post by Sean Huber in order to get the content of a file:
CREATE FUNCTION file_read(file text) RETURNS void AS $$ DECLARE content text; tmp text; BEGIN file := quote_literal(file); tmp := 'tmp_table'; EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)'; EXECUTE 'COPY ' || tmp || ' FROM ' || file; EXECUTE 'SELECT content FROM ' || tmp INTO content; **Do some more stuff here** EXECUTE 'DROP TABLE ' || tmp; END; $$ LANGUAGE plpgsql VOLATILE;
I'm not really happy with this as it is doing so much more work than necessary. I'd prefer not to create/drop relations cause all I really want to do is run Postgres' JSON functions against the content of some .json file. Does anyone know of a better way to do this without using psql?
If the file contains a valid JSON literal, you could read it in with
pg_read_file() and assign to a
json variable directly:
CREATE OR REPLACE FUNCTION file_read(file text) RETURNS void AS $func$ DECLARE content json := pg_read_file(file, 0, 10000000); -- arbitrary max. 10 MB BEGIN -- do some more stuff here END $func$ LANGUAGE plpgsql;
But that requires superuser privileges for reasons explained in the manual where I linked.
You could make that a
SECURITY DEFINER function, owned by a superuser, but be very careful who to grant the
EXECUTE privilege then. Example:
- Bash script to extract all specific key values from a unstructured JSON file
- Must numeric JSON keys be quoted?
- How to select blocks from a result by condition?
- Replace bracket (][) by ',' in Node JS
- Disable all constraints and table checks while restoring a dump
- Is it possible to hide a json array from the DOM?
- How do I parse a json data and output in html?
- How to sum of column by depending other column in my sql?
- JPA findBy field ignore case
- Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?