How to use the missing_ok flag of the pg_read functions?

user_287948 0
Viewed 59

Seems a bug, because is impossible to call pg_read_file or pg_read_binary_file without the length before the missing_ok flag... It is possible? I try lengths NULL and -1, but not works.

No clues at documentation, https://www.postgresql.org/docs/current/functions-admin.html

Notes

  • the length (file size in bytes) is unpredictable, sometimes big big files.
  • I need portable calls: for example pg_read_file(f,0,922337203,missing_ok) is not a solution.
  • (edited after @LaurenzAlbe comment) As a constant/default length I try also 9223372036854775807, and same problem, an error.
    In this case "ERROR: requested length too large".
filesystems postgresql
1 Answers

That is not a bug, because it works as documented. But I understand that it bugs you.

I read the code, and there is no way to omit the length or offset if you want to specify missing_ok. You could write a patch for PostgreSQL that allows that; it could be a version of the function with two arguments.

But it is easy to write your own function:

CREATE FUNCTION read_file(filename text, missing_ok boolean) RETURNS text
   LANGUAGE plpgsql STRICT AS
$$DECLARE
   v_result bytea := '';
   v_chunk bytea;
   v_offset bigint := 0;
BEGIN
   LOOP
      v_chunk = pg_read_binary_file(filename, v_offset, 10485760, missing_ok);
      EXIT WHEN v_chunk = '';
      v_result := v_result || v_chunk;
      v_offset := v_offset + length(v_chunk);
   END LOOP;
   RETURN convert_from(v_result, current_setting('server_encoding'));
END;$$;

The function reads the file in chunks of 10MB. The result is collected as bytea first to avoid errors if a chunk ends in the middle of a multi-byte character.

user_64643080
Related