0

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?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
knowads
  • 103
  • 4
  • 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 –  Apr 05 '19 at 06: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 Apr 05 '19 at 06: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 –  Apr 05 '19 at 06:36
  • theoretically. But each json file is several megabytes – knowads Apr 05 '19 at 07:14

1 Answers1

2

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:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • superuser access should be fine. What I'm essentially trying to do is something like Doctor Eval did here https://stackoverflow.com/a/48396608/4781181 where he used \set to make content the cat of a json file but without a psql client – knowads Apr 05 '19 at 04:05