Let's try to make the following SQL statement work:
We can start by creating the file
/tmp/test.txt with the following contents:
The next step is to create a simple
plpgsql function named
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
tmpwith a single field named
- Then we use PostgreSQL's COPY command to read the contents of
- Once the data has been imported, we
SELECTthe contents from the
tmptable and insert it
- Finally we
tmptable 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
- 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!