From: | "F(dot) BROUARD / SQLpro" <sqlpro(at)club-internet(dot)fr> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why did pg_relation_filepath does not give a correct path ? |
Date: | 2012-04-20 14:43:09 |
Message-ID: | 4F9175FD.3070309@club-internet.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 *************************
From | Date | Subject | |
---|---|---|---|
Next Message | Eliot Gable | 2012-04-20 15:27:22 | pg_advisory_lock() and row deadlocks |
Previous Message | Guillaume Lelarge | 2012-04-20 14:18:23 | Re: Why did pg_relation_filepath does not give a correct path ? |