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 $$
  content text;
  tmp text;
  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**


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?

  • Would it be possible to always store the file under the same name on the server? Then you could create an external table through file_fdw which would "always" be there. Or if you can put them on a webserver (jetty), maybe using the http extension would be another alternative
    – user1822
    Commented Apr 5, 2019 at 6:13
  • it wouldn't always be the same name, but the name would be deterministic - the file name will follow a given pattern based on information from the system catalog tables
    – knowads
    Commented Apr 5, 2019 at 6:34
  • Could you concatenate all files into a single (static) one (one line per JSON), with an additional column that identifies the original file name? Then the file_fdw approach might work
    – user1822
    Commented Apr 5, 2019 at 6:36
  • theoretically. But each json file is several megabytes
    – knowads
    Commented Apr 5, 2019 at 7:14

1 Answer 1


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)
   content json := pg_read_file(file, 0, 10000000);  -- arbitrary max. 10 MB 
   -- do some more stuff here
$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:

  • superuser access should be fine. What I'm essentially trying to do is something like Doctor Eval did here stackoverflow.com/a/48396608/4781181 where he used \set to make content the cat of a json file but without a psql client
    – knowads
    Commented Apr 5, 2019 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.