public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin Pryzby <[email protected]>
Subject: [PATCH v16 07/10] Add pg_ls_dir_recurse to show dir recursively..
Date: Sun, 8 Mar 2020 22:52:14 -0500
..possibly there's a better place to put this, like maybe a doc-only example ?
Need catversion bumped ?
---
doc/src/sgml/func.sgml | 27 ++++++++++++++++++++
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, 54 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c3a6b3277f..3a2ad3eb50 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25311,6 +25311,18 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
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>
@@ -25422,6 +25434,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,
+LATERAL format('/PG_%s_%s', left(current_setting('server_version_num'), 2), pcs.catalog_version_no) AS suffix) AS dir,
+LATERAL 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 1c77430f0c..215fba3b7d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1468,6 +1468,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 adfce45d1a..f94f403475 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10916,6 +10916,12 @@
proallargtypes => '{text,text,int8,timestamptz,bool}', proargmodes => '{i,o,o,o,o}',
proargnames => '{dirname,name,size,modification,isdir}',
prosrc => 'pg_ls_dir_metadata_1arg' },
+{ oid => '5034', 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, lateral 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 64b1417fb8..4188d684f0 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -243,6 +243,20 @@ select * from pg_ls_dir_metadata('.') limit 0;
------+------+--------------+-------
(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 372345720d..6041c4f3dc 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -76,6 +76,12 @@ select name, isdir from pg_ls_dir_metadata('.', false, false) where name='.'; --
-- Check that expected columns are present
select * from pg_ls_dir_metadata('.') limit 0;
+-- 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
--2FkSFaIQeDFoAt0B
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v16-0008-pg_ls_logdir-to-ignore-error-if-initial-top-dir-.patch"
view thread (52+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v16 07/10] Add pg_ls_dir_recurse to show dir recursively..
In-Reply-To: <no-message-id-1878528@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox