如何在PostgreSQL的字段中插入文本文件？ [英] How to insert a text file into a field in 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屋！