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?

  • 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 – a_horse_with_no_name Apr 5 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 Apr 5 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 – a_horse_with_no_name Apr 5 at 6:36
  • theoretically. But each json file is several megabytes – knowads Apr 5 at 7:14
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:

  • 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 Apr 5 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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