Subject: [PATCH v13 6/8] Add pg_ls_dir_recurse to show dir recursively..

From: Justin Pryzby <[email-masked]>
Date: Sun, 8 Mar 2020 22:52:14 -0500

..possibly there's a better place to put this, like maybe a doc-only example ?

XXX: this test case is unstable, if backends/autovacuum remove FSM...
ERROR:  could not stat file "./base/16384/20345_fsm": No such file or directory
CONTEXT:  SQL function "pg_ls_dir_recurse" statement 1

Need catversion bumped ?
---
 doc/src/sgml/func.sgml                       | 25 ++++++++++++++++++++
 src/backend/catalog/system_views.sql         |  1 +
 src/include/catalog/pg_proc.dat              |  6 +++++
 src/test/regress/expected/misc_functions.out | 14 +++++++++++
 src/test/regress/sql/misc_functions.sql      |  6 +++++
 5 files changed, 52 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4cdd03610a..0763db3f50 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -21351,6 +21351,16 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
         For each file in a directory, list the file and its metadata.  Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
        </entry>
       </row>
+      <row>
+       <entry>
+        <literal><function>pg_ls_dir_recurse(<parameter>dirname</parameter> <type>text</type>)</function></literal>
+       </entry>
+       <entry><type>setof text</type></entry>
+       <entry>
+         Call pg_ls_dir_metadata to recursively list the files in the specified directory, along with each file's metadata.
+         Restricted to superusers by default, but other users can be granted EXECUTE to run the function.
+       </entry>
+      </row>
       <row>
        <entry>
         <literal><function>pg_ls_logdir()</function></literal>
@@ -21462,6 +21472,21 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
     directory along with the file's metadata.
    </para>
 
+   <indexterm>
+    <primary>pg_ls_dir_recurse</primary>
+   </indexterm>
+   <para>
+    <function>pg_ls_dir_recurse</function> recursively lists the files 
+    in the specified directory.
+    To recursively list temporary directories in all tablespaces:
+<programlisting>
+SELECT * FROM (SELECT DISTINCT COALESCE(NULLIF(pg_tablespace_location(b.oid),'')||suffix, 'base/pgsql_tmp') AS dir
+FROM pg_tablespace b, pg_control_system() pcs,
+format('/PG_%s_%s', left(current_setting('server_version_num'), 2), catalog_version_no) AS suffix) AS dir,
+pg_ls_dir_recurse(dir) AS a;
+</programlisting>
+   </para>
+
    <indexterm>
     <primary>pg_ls_logdir</primary>
    </indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 05a644a7c9..e7295d8aaf 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1436,6 +1436,7 @@ REVOKE EXECUTE ON FUNCTION pg_stat_file(text,boolean) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_dir(text) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
 REVOKE EXECUTE ON FUNCTION pg_ls_dir_metadata(text,boolean,boolean) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_dir_recurse(text) FROM public;
 
 --
 -- We also set up some things as accessible to standard roles.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be3e820a03..b3509f0e12 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10747,6 +10747,12 @@
   proallargtypes => '{text,bool,bool,text,int8,timestamptz,bool}', proargmodes => '{i,i,i,o,o,o,o}',
   proargnames => '{dirname,missing_ok,include_dot_dirs,name,size,modification,isdir}',
   prosrc => 'pg_ls_dir_metadata' },
+{ oid => '8511', descr => 'list all files in a directory recursively',
+  proname => 'pg_ls_dir_recurse', prorows => '10000', proretset => 't',
+  provolatile => 'v', prorettype => 'record', proargtypes => 'text',
+  proallargtypes => '{text,text,int8,timestamptz,bool}',
+  proargnames => '{dirname,name,size,modification,isdir}', proargmodes => '{i,o,o,o,o}',
+  prolang => 'sql', prosrc => "with recursive ls as (select * from pg_ls_dir_metadata(dirname, true, false) union all select ls.name||'/'||a.name, a.size, a.modification, a.isdir from ls, pg_ls_dir_metadata(dirname||'/'||ls.name, false, false)a where ls.isdir) select * from ls" },
 
 # hash partitioning constraint function
 { oid => '5028', descr => 'hash partition CHECK constraint',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 3ca4b85dd9..ae8716a83f 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -208,6 +208,20 @@ select * from pg_ls_tmpdir() where name='Does not exist';
 ------+------+--------------+-------
 (0 rows)
 
+-- Check that we at least succeed in recursing once, and that we don't show the leading dir prefix
+SELECT name, isdir FROM pg_ls_dir_recurse('.') WHERE isdir AND name~'^pg_wal';
+         name          | isdir 
+-----------------------+-------
+ pg_wal                | t
+ pg_wal/archive_status | t
+(2 rows)
+
+-- Check that expected columns are present
+SELECT * FROM pg_ls_dir_recurse('.') LIMIT 0;
+ name | size | modification | isdir 
+------+------+--------------+-------
+(0 rows)
+
 --
 -- Test adding a support function to a subject function
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index f09c890b5d..7038bcdb0f 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -64,6 +64,12 @@ select count(*) > 0 from
 -- The name='' condition is never true, so the function runs to completion but returns zero rows.
 select * from pg_ls_tmpdir() where name='Does not exist';
 
+-- Check that we at least succeed in recursing once, and that we don't show the leading dir prefix
+SELECT name, isdir FROM pg_ls_dir_recurse('.') WHERE isdir AND name~'^pg_wal';
+
+-- Check that expected columns are present
+SELECT * FROM pg_ls_dir_recurse('.') LIMIT 0;
+
 --
 -- Test adding a support function to a subject function
 --
-- 
2.17.0

--E0h0CbphJD8hN+Gf
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v13-0007-pg_ls_logdir-to-ignore-error-if-initial-top-dir-.patch"