PostgreSQL：从文件系统加载文件而不插入表

发布于 2024-12-07 16:24:44 字数 150 浏览 0 评论 0 原文

我该如何做这样的事情？
在 mysql 中我这样做：

SELECT LOAD_FILE('/path/to/file');

postgres 怎么样？不使用psql的\copy命令？

原文

How do I do such a thing?
In mysql I do:

SELECT LOAD_FILE('/path/to/file');

What about postgres? Without using the \copy command of psql?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

请叫√我孤独 2024-12-14 16:24:44

这取决于你到底想做什么。

您有COPY用于将结构化数据读入（临时) 表。
_{注意，这是SQL命令，与psql的\copy命令类似，但不一样！}
而且还有pg_read_file() 用于读取任何文本文件。

编辑 - 一个基本示例：

CREATE FUNCTION f_showfile(myfile text)
  RETURNS text AS
$x$
BEGIN

RETURN pg_read_file(myfile, 0, 1000000); -- 1 MB max.
-- or you could read into a text var and do stuff with it.

END;
$x$
  LANGUAGE plpgsql VOLATILE;

只有超级用户才能使用此功能。小心不要打开安全漏洞。您可以使用 SECURITY DEFINER、REVOKE FROM公共角色和GRANT TO所选角色。如果安全是一个问题，请阅读提供的链接中的本段：

安全地编写 SECURITY DEFINER 函数

pg_read_file() 您只能从日志文件目录和数据库目录中读取。在Linux上，您可以创建一个到数据目录（在安全位置）的符号链接，如下所示：

cd /path//my/database
ln -s /var/lib/postgresql/text_dir/ .

然后像这样调用：

SELECT f_showfile('text_dir/readme.txt');

输出：

                                            f_showfile
-------------------------------------------------------------------------
 This is my text from a file.

That depends what you want to do exactly.

You have COPY for reading structured data into (temporary) tables.
_{Note that this is the SQL command, which is similar, but not the same as the \copy command of psql!}
And there is pg_read_file() for reading in any text file.

Edit - a basic example:

CREATE FUNCTION f_showfile(myfile text)
  RETURNS text AS
$x$
BEGIN

RETURN pg_read_file(myfile, 0, 1000000); -- 1 MB max.
-- or you could read into a text var and do stuff with it.

END;
$x$
  LANGUAGE plpgsql VOLATILE;

Only superusers can use this function. Be careful not to open security holes. You could create a function with SECURITY DEFINER, REVOKE FROMpublic and GRANT TO selected roles. If security is an issue read this paragraph at the provided link:

Writing SECURITY DEFINER Functions Safely

pg_read_file() you can only read from the logfile dir and the database dir. On Linux you could create a symlink to a data dir (at a safe location) like this:

cd /path//my/database
ln -s /var/lib/postgresql/text_dir/ .

Then call like this:

SELECT f_showfile('text_dir/readme.txt');

Output:

                                            f_showfile
-------------------------------------------------------------------------
 This is my text from a file.

回复收藏 0 原文

~没有更多了~