Geek Answers Handbook

How to view files in a folder from inside Postgres?

Is there a way to list files from a folder?

Sort of:

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

I tried pg_ls_dir, but for documentation :

Only files in the database cluster directory and log_directorycan be accessed. Use the relative path for the files in the cluster directory and the path corresponding to the configuration setting log_directoryfor the log files. The use of these features is limited to superusers.

I need to list files from a folder outside of postgres directories, similar to how this is done with COPY.

+4
source share
3 answers

This is usually not useful for the SQL client.

, , script, 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;

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...
+5

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;

2000 81 , [ zip.

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;

PostgreSQL non-superuser, :

SELECT * FROM files();

.

SECURITY DEFINER PostgreSQL , . , , , .

SET client_min_messages TO WARNING; PostgreSQL , . .

CREATE TEMP TABLE , . , TEMP.

'find...', /usr/bin/find, (type -f) , (-printf "%f\n"). , -maxdepth 1 - . . .

, , , . , PostgreSQL , , . , , . , .

+8

Extended version of this answer :

-- 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;
+4
source

More articles:


All Articles