postgresql - 如何从 Postgres 中列出文件夹中的文件？

Question

有什么方法可以列出文件夹中的文件吗？

类似于:

select * from pg_ls_dir('/home/christian')

我尝试了 pg_ls_dir 但是，per documentation :

Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files. Use of these functions is restricted to superusers.

我需要列出 postgres 目录之外的文件夹中的文件，类似于使用 COPY 完成的方式。

Answer 1

使用 PostgreSQL 9.3，可以避免安装语言扩展的开销:

DROP TABLE IF EXISTS files;
CREATE TABLE files(filename text);
COPY files FROM PROGRAM 'find /usr/bin -maxdepth 1 -type f -printf "%f\n"'; 
SELECT * FROM files ORDER BY filename ASC;

创建一个包含 2,000 多行的表，从 [ 到 zip。

通常 COPY 命令需要 super 用户权限。由于文件系统的路径是硬编码的(即，不是来自用户的未经过滤的值)，首先使用 super 用户帐户定义函数(例如， postgres) 如下:

CREATE OR REPLACE FUNCTION files()
  RETURNS SETOF text AS
$BODY$
BEGIN
  SET client_min_messages TO WARNING;
  DROP TABLE IF EXISTS files;
  CREATE TEMP TABLE files(filename text);
  COPY files FROM PROGRAM 'find /usr/bin -maxdepth 1 -type f -printf "%f\n"';
  RETURN QUERY SELECT * FROM files ORDER BY filename ASC;
END;
$BODY$
  LANGUAGE plpgsql SECURITY DEFINER;

使用非 super 用户帐户登录到 PostgreSQL，然后:

SELECT * FROM files();

应该返回相同的结果列表，没有任何安全违规错误。

---

SECURITY DEFINER 告诉 PostgreSQL 在用于创建该函数的帐户角色下运行该函数。由于它是使用 super 用户角色创建的，因此无论执行命令的角色是什么，它将以 super 用户权限执行。

SET client_min_messages TO WARNING; 告诉 PostgreSQL 在无法删除表时抑制消息。删除这一行就可以了。

CREATE TEMP TABLE 用于创建一个不需要随时间保留的表。如果您需要永久表，请删除 TEMP 修饰符。

'find...' 命令，也可以是 /usr/bin/find，只列出文件(type -f) 并仅显示文件名，没有前导路径，每行一个文件名分隔 (-printf "%f\n")。最后，-maxdepth 1 将文件搜索限制为仅指定目录而不搜索任何子目录。参见 find's man page了解详情。

---

这种方法的一个缺点是似乎没有办法参数化要执行的命令。 PostgreSQL 似乎要求它是文本字符串，而不是表达式语句。也许这是最好的，因为它可以防止执行任意命令。所见即所得。