How to insert a text file into a field in PostgreS

问题:

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.

回答1:

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');

回答2:

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 <data_directory>/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'));

回答3:

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.

回答4:

Use COPY instead of INSERT

reference: http://www.commandprompt.com/ppbook/x5504#AEN5631