如何在PostgreSQL的字段中插入文本文件? [英] How to insert a text file into a field in PostgreSQL?
本文介绍了如何在PostgreSQL的字段中插入文本文件?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在PostgreSQL的字段中插入文本文件?
我想插入一行来自本地或远程文本文件的字段。
我希望有一个像gettext()或geturl()这样的函数来执行以下操作:
%INSERT INTO collection(id,path,content)VALUES(1,'/ etc / motd',gettext('/ etc / motd'));
- S。
解决方案
感谢提示。我发现了另一个内置函数的答案。
您需要拥有超级用户权限才能执行该操作!
- 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'));
这篇关于如何在PostgreSQL的字段中插入文本文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文