如何在仅装plpgsql的PostgreSQL中于Windows/Linux执行CLI命令及处理服务器文件

一、仅用plpgsql执行CLI命令（超级用户权限） #

PostgreSQL默认的plpgsql本身没有直接执行系统命令的语法，但可以通过COPY ... TO PROGRAM语法间接调用系统命令，这是PostgreSQL内置功能，无需额外扩展。

Linux系统示例 #

删除文件的plpgsql函数

CREATE OR REPLACE FUNCTION delete_file_linux(file_path text)
RETURNS void AS $$
BEGIN
    EXECUTE format('COPY (SELECT 1) TO PROGRAM ''rm -f %L'';', file_path);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

注意：SECURITY DEFINER会让函数以创建者身份执行，需确保创建者是超级用户；%L用于安全转义路径，避免SQL注入风险。

下载文件到客户端（将服务器文件内容导出到本地）

如果要把服务器文件下载到客户端，可直接在客户端执行psql命令（无需创建函数）：

psql -d your_database_name -c "COPY (SELECT pg_file_read('/absolute/path/to/server/file')) TO STDOUT" > local_file.txt

Windows系统示例 #

删除文件的plpgsql函数

CREATE OR REPLACE FUNCTION delete_file_windows(file_path text)
RETURNS void AS $$
BEGIN
    EXECUTE format('COPY (SELECT 1) TO PROGRAM ''cmd /c del /f /q %L'';', file_path);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

用cmd /c调用DOS命令，/f强制删除只读文件，/q静默执行不弹出确认提示。

二、无需CLI命令的文件操作方法（超级用户权限） #

PostgreSQL内置了一组文件操作函数，无需调用外部命令即可完成操作：

• 删除文件：使用pg_file_unlink(file_path text)

  -- Linux系统示例
  SELECT pg_file_unlink('/var/lib/postgresql/extra/file.txt');
  -- Windows系统示例（注意反斜杠需转义）
  SELECT pg_file_unlink('C:\\Program Files\\PostgreSQL\\15\\data\\temp.txt');
  

• 读取服务器文件（实现下载）：使用pg_file_read(file_path text, offset bigint, length bigint)，结合COPY导出到客户端：

  -- 读取整个文件并保存到本地
  COPY (SELECT pg_file_read('/path/to/server/file', 0, pg_file_size('/path/to/server/file'))) TO STDOUT > local_copy.txt
  

• 查看目录文件列表：用pg_ls_dir(dir_path text)定位目标文件：

  SELECT pg_ls_dir('/var/lib/postgresql/data');
  

三、其他替代方案 #

• 直接操作系统层面操作：最安全高效的方式，通过SSH（Linux）、远程桌面/PowerShell远程（Windows）登录服务器，直接执行文件删除或下载操作，无需通过PostgreSQL中转。
• 大对象（LO）操作：如果文件原本存储在PostgreSQL大对象中，可通过lo_export命令将大对象导出到服务器本地，或用客户端工具（如psql的\lo_export）下载到本地；删除大对象使用lo_unlink。

内容的提问来源于stack exchange，提问作者user25383184