更新时间:2023-11-14 23:17:22
感谢提示。我发现了另一个内置函数的答案。
您需要拥有超级用户权限才能执行该操作!
- 1.创建一个函数来加载doc
- DROP FUNCTION get_text_document(CHARACTER VARYING);
创建或替换函数get_text_document(p_filename CHARACTER VARYING)
RETURNS TEXT AS $$
- 将读取结尾设置为一个大数字,因为我们懒得抓住长度
- 它会在EOF处剪切
SELECT CAST(pg_read_file(E'mydocuments /'|| $ 1,0,100000000)AS TEXT);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;
ALTER FUNCTION get_text_document(CHARACTER VARYING)拥有者postgres;
- 2.通过以超级用户身份运行来确定群集的位置:
SELECT name,setting FROM pg_settings WHERE name ='data_directory';
- 3.将要导入的文件复制到< data_directory> / mydocuments /
- 并测试它:
SELECT get_text_document('file1.txt') ;
$ b $ 4.现在执行import(HINT:File必须是UTF-8)
INSERT INTO mytable(文件,内容)
VALUES('file1.txt', get_text_document( 'FILE1.TXT'));
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.
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'));