View to get dead files

/*  At row 71 the directory PG_9.2_201204301 is used to build the tablespace's path.
    This is specific for postgresql 9.2. To build a correct path for previous
     versions the name need to be adapted.
    A case construct over the SELECT substr(version(),12,3); can automate the path's build
*/
DROP VIEW IF EXISTS v_dead_files;
CREATE OR REPLACE VIEW v_dead_files AS
SELECT
    t_file,
    t_node,
    t_db_path,
    t_rel_node,
    (pg_stat_file(t_db_path||'/'||t_file)).modification AS ts_modification,
    pg_size_pretty((pg_stat_file(t_db_path||'/'||t_file)).size) as t_size
FROM
(
    SELECT
        t_file,
        CASE
            WHEN
                    split_part(t_file,'.',2)<>''
                THEN
                    'Extent number '||split_part(t_file,'.',2)||' for file '||split_part(t_file,'.',1)
            WHEN
                    split_part(t_file,'_',2)='fsm'
                THEN
                    'Free space map  for file '||split_part(t_file,'_',1)
            WHEN
                    split_part(t_file,'_',2)='vm'
                THEN
                    'Visibility map for file '||split_part(t_file,'_',1)
            WHEN
                    split_part(t_file,'_',2)=''
                OR  split_part(t_file,'.',2)=''
                THEN
                    t_file
        END AS t_node,
        t_db_path,
        CASE
            WHEN
                    split_part(t_file,'.',2)<>''
                THEN
                    split_part(t_file,'.',1)
            WHEN
                    split_part(t_file,'_',2)<>''
                THEN
                    split_part(t_file,'_',1)
            WHEN
                    split_part(t_file,'_',2)=''
                OR  split_part(t_file,'.',2)=''
                THEN
                    t_file
        END AS t_rel_node
    FROM
    (
        SELECT
            pg_ls_dir(t_db_path) AS t_file,
            t_db_path
        FROM
        (
            SELECT
                CASE
                    WHEN t_dir='base'
                    THEN
                        t_dir||'/'||t_dat_dir
                    WHEN t_dir='global'
                    THEN
                        t_dir

                    ELSE
                        'pg_tblspc/'||t_dir||'/PG_9.2_201204301/'||t_dat_dir

                END AS t_db_path,
                t_dat_dir,
                t_dir
            FROM
            (
                SELECT
                    oid::text as t_dat_dir
                FROM
                    pg_database
                WHERE
                    datname=current_database()

            ) t_dat,
            (

                SELECT
                    CASE
                        WHEN
                            spcname='pg_default'
                        THEN
                            'base'::text
                        WHEN
                            spcname='pg_global'
                        THEN
                            'global'::text
                        ELSE
                            oid::text
                    END AS t_dir
                FROM
                    pg_tablespace
                WHERE
                    oid IN
                            (
                                SELECT DISTINCT reltablespace FROM pg_class
                            )


            ) t_dir
        ) t_lsdir
    ) t_files
) t_file_list

WHERE
    t_rel_node NOT IN
            (
                SELECT pg_relation_filenode(oid)::text FROM pg_class WHERE pg_relation_filenode(oid) IS NOT NULL
              )
    AND t_file NOT IN (
                'pg_filenode.map',
                'PG_VERSION',
                'pg_internal.init',
                'pg_control'
                )
ORDER BY
    t_rel_node::integer DESC

    ;