Обсуждение: list files and sizes

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.

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

On 2016-02-20 21:59, Joe Conway wrote:

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

Thanks Joe, that's great.

I'd been looking at LATERAL joins, wondering if they could be of use
here, but am still at the stage where the syntax looks baffling :) I'll
have a look for some tutorials.

On 2016-02-20 21:40, 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

Now Joe showed me how to use LATERAL to achieve this, I've got an
initial build of a little application I've been messing with.

pgprofiler is like a simple version of MSSQL Profiler:

http://www.xentu.com/pgprofiler/

with source code at bitbucket. I've just built a win-32 version, but the
FPC/Lazarus source should let me compile to other platforms...