Let's try to make the following SQL statement work:

SELECT file.read('/tmp/test.txt');

We can start by creating the file /tmp/test.txt with the following contents:

Hello PostgreSQL!

The next step is to create a simple plpgsql function named file.read!

CREATE FUNCTION file.read(file text)  
  RETURNS text AS $$
    DECLARE
      content text;
    BEGIN
      content := 'Static content for now!';
      RETURN content;
    END;
  $$ LANGUAGE plpgsql VOLATILE;

This initial placeholder function just returns static content so that we can make sure things are working properly so far.

SELECT file.read('/tmp/test.txt');

          read
------------------------
 Static content for now
(1 row)

Now we just need to fill in the function with logic to read from the filesystem!

CREATE FUNCTION file.read(file text)  
  RETURNS text AS $$
    DECLARE
      content text;
      tmp text;
    BEGIN
      file := quote_literal(file);
      tmp := quote_ident(uuid_generate_v4()::text);

      EXECUTE 'CREATE TEMP TABLE ' || tmp || ' (content text)';
      EXECUTE 'COPY ' || tmp || ' FROM ' || file;
      EXECUTE 'SELECT content FROM ' || tmp INTO content;
      EXECUTE 'DROP TABLE ' || tmp;

      RETURN content;
    END;
  $$ LANGUAGE plpgsql VOLATILE;

Now we should see the contents of the file that we created!

SELECT file.read('/tmp/test.txt');

          read
------------------------
 Hello PostgreSQL!
(1 row)

OK... so what's going on here? Let's break it down!

We begin by declaring a couple of variables using the text data type.

  • content - stores the contents of the file read from the filesystem
  • tmp - a unique string used as the name of a temporary table

Then we move into the BEGIN block and set a couple of variables.

  • file - the quoted file name that the function was called with
  • tmp - the quoted unique name for a temporary table

Once we have our variables all setup, we EXECUTE some SQL to read the file contents.

  • First we create a temporary table named tmp with a single field named content
  • Then we use PostgreSQL's COPY command to read the contents of file into the tmp table
  • Once the data has been imported, we SELECT the contents from the tmp table and insert it INTO the local content variable
  • Finally we DROP the tmp table since we don't need it anymore

This was pretty interesting to get working! PostgreSQL has some other ways to read files from the filesystem.

  • file_fdw - a read-only foreign data wrapper for filesystem access built on the COPY command
  • pg_read_file - this function is restricted to superusers and only allows files within the database cluster directory and the log_directory to be accessed
  • pgsql-fio - an extension for basic file system functions

Another interesting challenge could be to write a file.write function to save content to a file!