Re: list files and sizes

On 02/20/2016 01:40 PM, richard@xentu.com wrote:
> Could anyone tell me if this is possible?
>
> I can get a list of files from the pg_log directory using
>
> select * from pg_ls_dir('pg_log')
>
> For any one of those files, I could find out it's size using
>
> select * from pg_stat_file('pg_log\postgresql-2016-02-16_133214.log')
>
> but, what I'd like to have is a query that somehow combines those two
> and returns a recordset consisting of two columns, file_name & file_size
>
> I could probably write procedural code to do this, but am thinking it
> maybe can be done, somehow, in a single statement.

Something like this:

SELECT v.d, f.f, s.size, s.isdir
FROM (values('pg_xlog') ) AS v(d),
     LATERAL pg_ls_dir(v.d) AS f(f),
     LATERAL pg_stat_file(v.d || '/' || f.f) as s;
    d    |            f             |   size   | isdir
---------+--------------------------+----------+-------
 pg_xlog | archive_status           |     4096 | t
 pg_xlog | 000000010000000000000001 | 16777216 | f
(2 rows)

HTH,

Joe



--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development