Re: Why did pg_relation_filepath does not give a correct path ?

On Fri, 2012-04-20 at 14:47 +0200, F. BROUARD / SQLpro wrote:
> Le 20/04/2012 12:05, Guillaume Lelarge a écrit :
> > On Fri, 2012-04-20 at 11:35 +0200, F. BROUARD / SQLpro wrote:
> >> Hi,
> >>
> >> according to the documentation, the function pg_relation_filepath
> >> "returns the entire file path name (relative to the database cluster's
> >> data directory PGDATA) of the relation"
> >>
> >> When my table are located in the pg_default tablespace, the gievn
> >> relative path is correct
> >>
> >> When my table are located on a specific tablespace, this function
> >> returns incorrect dats such as :
> >>
> >> pg_tblspc/25310/PG_9.1_201105231/16594/25311
> >>
> >> only the "PG_9.1_201105231/16594/25311" is correct.
> >>
> >> What does the "pg_tblspc/25310" do ???
> >>
> >> How can I obtain the correct relative path ?
> >>
> >
> > It is the correct relative path. It's relative to $PGDATA. Your
> > tablespace has the OID 25310. So, you have a symbolic link (or junction
> > if you are on Windows) named 25310 in your $PGDATA/pg_tblspc directory.
> > And PostgreSQL will access this table via the symbolic link. It really
> > uses this path: pg_tblspc/25310/PG_9.1_201105231/16594/25311
>
> yes, but I want to have the real path, not the symbolic PG path...
>
> Do you know haw can I obtain it by a calssical SQL Query ?
>

Something like this might do the trick:

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

At least, it works on my 9.1.

BTW, please, keep the list posted.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com