How to insert a text file into a field in PostgreSQL?

Question

How to insert a text file into a field in PostgreSQL?

I'd like to insert a row with fields from a local or remote text file.

I'd expect a function like gettext() or geturl() in order to do the following:

% INSERT INTO collection(id, path, content) VALUES(1, '/etc/motd', gettext('/etc/motd'));

-S.

Answer 1

You can't. You need to write a program that will read file content's (or URL's) and store it into the desired field.

Answer 2

The easiest method would be to use one of the embeddable scripting languages. Here's an example using plpythonu:

CREATE FUNCTION gettext(url TEXT) RETURNS TEXT
AS $$
import urllib2
try:
  f = urllib2.urlopen(url)
  return ''.join(f.readlines())
except Exception:
  return ""
$$ LANGUAGE plpythonu;

One drawback to this example function is its reliance on urllib2 means you have to use "file:///" URLs to access local files, like this:

select gettext('file:///etc/motd');

Answer 3

Thanks for the tips. I've found another answer with a built in function. You need to have super user rights in order to execute that!

-- 1. Create a function to load a doc -- DROP FUNCTION get_text_document(CHARACTER VARYING); CREATE OR REPLACE FUNCTION get_text_document(p_filename CHARACTER VARYING) RETURNS TEXT AS $$ -- Set the end read to some big number because we are too lazy to grab the length -- and it will cut of at the EOF anyway SELECT CAST(pg_read_file(E'mydocuments/' || $1 ,0, 100000000) AS TEXT); $$ LANGUAGE sql VOLATILE SECURITY DEFINER; ALTER FUNCTION get_text_document(CHARACTER VARYING) OWNER TO postgres;

-- 2. Determine the location of your cluster by running as super user: SELECT name, setting FROM pg_settings WHERE name='data_directory';

-- 3. Copy the files you want to import into /mydocuments/ -- and test it: SELECT get_text_document('file1.txt');

-- 4. Now do the import (HINT: File must be UTF-8) INSERT INTO mytable(file, content) VALUES ('file1.txt', get_text_document('file1.txt'));