Exploring the Database - PostgreSQL 9 Administration - page 46

filename TEXT;

datadir TEXT;

i INTEGER := 0;

tablesize BIGINT;

BEGIN

--

-- get data directory

--

EXECUTE 'SHOW data_directory' INTO datadir;

--

-- get relfilenode and reltablespace

--

SELECT

reltablespace as tsid

,relfilenode as rid

INTO classoutput

FROM pg_class

WHERE oid = tablename

AND relkind = 'r';

--

-- Throw an error if we can't find the tablename specified

--

IF NOT FOUND THEN

RAISE EXCEPTION 'tablename % not found', tablename;

END IF;

tsid := classoutput.tsid;

rid := classoutput.rid;

--

-- get the database object identifier (oid)

--

SELECT oid INTO dbid

FROM pg_database

WHERE datname = current_database();

--

-- Use some internals knowledge to set the filepath

--

IF tsid = 0 THEN

filepath := datadir || '/base/' || dbid || '/' || rid;

ELSE

filepath := datadir || '/pg_tblspc/'

|| tsid || '/'

|| dbid || '/'

|| rid;

END IF;