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

How to list files in a folder from inside Postgres?

提问by Christian

Is there any way to list files from a folder?

有没有办法从文件夹中列出文件？

Something like:

就像是：

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

I tried pg_ls_dirbut, per documentation:

我试过了，pg_ls_dir但是，根据文档：

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

只能log_directory访问数据库集群目录和 中的文件。使用集群目录中文件的相对路径，以及与log_directory日志文件的配置设置匹配的路径。这些功能的使用仅限于超级用户。

I need to list files from a folder outside the postgres directories, similar to how it's done with COPY.

我需要列出 postgres 目录之外的文件夹中的文件，类似于使用COPY.

采纳答案by Daniel Vérité

It's normally not useful for a SQL client.

它通常对 SQL 客户端没有用。

Anyway should you need to implement it, that's a typical use case for a script language like plperlu. Example:

无论如何，如果您需要实现它，这是脚本语言的典型用例，例如plperlu. 例子：

CREATE FUNCTION nosecurity_ls(text) RETURNS setof text AS $$
  opendir(my $d, $_[0]) or die $!;
  while (my $f=readdir($d)) {
    return_next($f);
  }
  return undef; 
$$ language plperlu;

That's equivalent to the pg_ls_dir(text)function mentioned in System Administration Functionsexcept for the restrictions.

除了限制之外，这相当于系统管理功能中pg_ls_dir(text)提到的功能。

---

---

  => select * from nosecurity_ls('/var/lib/postgresql/9.1/main') as ls;

      ls      
-----------------
 pg_subtrans
 pg_serial
 pg_notify
 pg_clog
 pg_multixact
 ..
 base
 pg_twophase
 etc...

回答by Dave Jarvis

Using PostgreSQL 9.3, it is possible to avoid the overhead of installing a language extension:

使用 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;

Creates a table with 2,000+ rows in 81 msec on my machine, from [to zip.

在我的机器上在 81 毫秒内创建一个包含 2,000 多行的表，从[到zip.

Normally the COPYcommand requires superuser privileges. Since the path to the file system is hard-coded (i.e., not an unsanitized value from users), it doesn't pose a great security risk to define the function first using a superuser account(e.g., postgres) as follows:

通常该COPY命令需要超级用户权限。由于文件系统的路径是硬编码的（即，不是来自用户的未经处理的值），因此首先使用超级用户帐户（例如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;

Log into PostgreSQL using a non-superuser account, then:

使用非超级用户帐户登录 PostgreSQL，然后：

SELECT * FROM files();

The same list of results should be returned without any security violation errors.

应该返回相同的结果列表，而不会出现任何安全违规错误。

---

---

The SECURITY DEFINERtells PostgreSQL to run the function under the role of the account that was used to create the function. Since it was created using a superuser role, it will execute with superuser permissions, regardless of the role that executes the command.

该SECURITY DEFINER告诉PostgreSQL的到被用来创建功能的帐户的作用下运行的功能。由于它是使用超级用户角色创建的，因此无论执行命令的角色如何，它都会以超级用户权限执行。

The SET client_min_messages TO WARNING;tells PostgreSQL to suppress messages if the table cannot be dropped. It's okay to delete this line.

该SET client_min_messages TO WARNING;告诉PostgreSQL的打压消息，如果该表不能被丢弃。删除这一行就可以了。

The CREATE TEMP TABLEis used to create a table that does not need to persist over time. If you need a permanent table, remove the TEMPmodifier.

将CREATE TEMP TABLE用于创建并不需要坚持一段时间的表。如果您需要永久表，请删除TEMP修饰符。

The 'find...'command, which could also be /usr/bin/find, lists only files (type -f) and displays only the filename without the leading path separated one filename per line (-printf "%f\n"). Finally, -maxdepth 1limits the file search to only the specified directory without searching any subdirectories. See find's man pagefor details.

该'find...'命令也可以是/usr/bin/find，仅列出文件 ( type -f) 并仅显示文件名，而不用前导路径分隔每行一个文件名 ( -printf "%f\n")。最后，-maxdepth 1将文件搜索限制为仅指定目录，而不搜索任何子目录。有关详细信息，请参阅find 的手册页。

---

---

One disadvantage to this approach is that there doesn't seem to be a way to parameterize the command to execute. It seems that PostgreSQL requires it to be a text string, rather than an expression statement. Perhaps this is for the best as it prevents allowing arbitrary commands to be executed. What you see is what you execute.

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

回答by Hubbitus

Extended version of this answer, function ls_files_extended:

此答案的扩展版本，函数ls_files_extended：

-- Unfortunately that variant only allow use hardcoded path
-- To use user parameter we will use dynamic EXECUTE.
-- Return also file size and allow filtering
--
-- @param path text. Filesystem path for read to
-- @param filter text (default null meaning return all). Where condition to filter files. F.e.: $$filename LIKE '0%'$$
-- @param sort text (default filename).
--
-- Examples of use:
-- 1) Simple call, return all files, sort by filename:
-- SELECT * FROM ls_files_extended('/pg_xlog.archive')
-- 2) Return all, sort by filesize:
-- SELECT * FROM ls_files_extended('/pg_xlog.archive', null, 'size ASC')
-- 3) Use filtering and sorting:
-- SELECT * FROM ls_files_extended('/pg_xlog.archive', 'filename LIKE ''0%''', 'size ASC')
-- or use $-quoting for easy readability:
-- SELECT * FROM ls_files_extended('/pg_xlog.archive', $$filename LIKE '0%'$$, 'size ASC')
CREATE OR REPLACE FUNCTION ls_files_extended(path text, filter text default null, sort text default 'filename')
    RETURNS TABLE(filename text, size bigint) AS
$BODY$
BEGIN
  SET client_min_messages TO WARNING;
  CREATE TEMP TABLE _files(filename text, size bigint) ON COMMIT DROP;

  EXECUTE format($$COPY _files FROM PROGRAM 'find %s -maxdepth 1 -type f -printf "%%f\t%%s\n"'$$, path);

  RETURN QUERY EXECUTE format($$SELECT * FROM _files WHERE %s ORDER BY %s $$, concat_ws(' AND ', 'true', filter), sort);
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;