Query to get current temp files in PostgreSQL (needs SUPERUSER)

temp_files.sql

WITH temp_dirs AS (
    SELECT s1.dir || s1.version || '/pgsql_tmp' AS dir_name
    FROM
        (
            SELECT
                'pg_tblspc/' || spc.oid::text || '/' AS dir,
                l.version
            FROM
                (
                    SELECT spc1.oid
                    FROM
                        pg_tablespace spc1
                    WHERE
                        spc1.spcname NOT LIKE 'pg_%' OFFSET 0
                ) AS spc
                CROSS JOIN LATERAL pg_ls_dir('pg_tblspc/' || spc.oid::text || '/') AS l(version)
            WHERE
                l.version LIKE 'PG_' || regexp_replace(current_setting('server_version'), '(\.[0-9]+|[a-z0-9])$', '') || '%'
        ) AS s1
    WHERE
        EXISTS(
            SELECT 1
            FROM pg_ls_dir(s1.dir || s1.version) AS l(item)
            WHERE l.item = 'pgsql_tmp'
        )
    UNION ALL
    SELECT
        'base/pgsql_tmp'
    WHERE
        EXISTS(
            SELECT 1
            FROM pg_ls_dir('base/') AS l(item)
            WHERE l.item = 'pgsql_tmp'
        )
)
SELECT
    td.dir_name,
    l.temp_file,
    substring(l.temp_file FROM 'pgsql_tmp([0-9]+).[0-9]+') AS pid,
    stat.*
FROM
    temp_dirs AS td
    CROSS JOIN LATERAL pg_ls_dir(td.dir_name) AS l(temp_file)
    CROSS JOIN LATERAL pg_stat_file(td.dir_name || '/' || l.temp_file) AS stat
;