Re: Why did pg_relation_filepath does not give a correct path ? - Mailing list pgsql-general

Le 20/04/2012 16:18, Guillaume Lelarge a écrit :
> SELECT
>    CASE
>      WHEN coalesce(t.spclocation, '') = ''
>          THEN
>      current_setting('data_directory')||'/'||pg_relation_filepath(c.oid)
>           ELSE replace(pg_relation_filepath(c.oid),
>                        'pg_tblspc/'||t.oid::text,
>                        t.spclocation)
>    END AS filepath
> FROM pg_class AS c
> LEFT JOIN pg_tablespace AS t ON c.reltablespace=t.oid
> WHERE
>    c.relname='pg_class';

I was at the same point, doing another way :

COALESCE(spclocation, setting)  ||
           CASE
              WHEN T.spcname <> 'pg_global'
                 THEN SUBSTRING(REPLACE(pg_relation_filepath(C.oid),
'pg_tblspc/', ''),
                                POSITION('/' IN
REPLACE(pg_relation_filepath(C.oid), 'pg_tblspc/', '')),

CHARACTER_LENGTH(REPLACE(pg_relation_filepath(C.oid), 'pg_tblspc/', '')))
              WHEN T.spcname = 'pg_global'
                 THEN (SELECT setting FROM pg_settings WHERE name =
'data_directory')
                       || '/' || pg_relation_filepath(C.oid)
              ELSE '/' || pg_relation_filepath(C.oid) END AS location


... using pg_setting

This returns the same datas

A +


--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************