如何从Postgres内部列出文件夹中的文件? [英] How to list files in a folder from inside Postgres?

问题描述

有没有办法列出文件夹中的文件?

类似于:

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

我尝试了 pg_ls_dir 但是，每个文档:

<块引用>

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

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

解决方案

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

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

CREATE FUNCTION nosecurity_ls(text) RETURNS setof text AS $$opendir(my $d, $_[0]) 否则死 $!;而 (我的 $f=readdir($d)) {return_next($f);}返回 undef;$$ 语言 plperlu;

这相当于 pg_ls_dir(text) 函数"noreferrer">系统管理功能 限制除外.

<小时>

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

<前>ls-----------------pg_subtranspg_serialpg_notifypg_clogpg_multixact..根据pg_twophase等等...

Is there any way to list files from a folder?

Something like:

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

I tried pg_ls_dir but, 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.

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

解决方案

It's normally not useful for a SQL client.

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

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 Functions except for the restrictions.

---

  => 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...

这篇关于如何从Postgres内部列出文件夹中的文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！