Postgresql/SQL Run System Catalog function in Select Query

Question

I have a select statement that is pulling back database information such as name, owner, owner's superuser status and tablespace. For Postgresql versions less than 9.2 I was also able to pull the tablespace data locations but with 9.2 they've changed it so that it is a function call instead. My current query is:

SELECT pg_database.datname as Database_Name, pg_authid.rolname as Database_Owner, 
pg_authid.rolsuper as IsSuperUser, pg_tablespace.spcname as TableSpaceName, 
pg_catalog.pg_tablespace_location(oid) as TableSpaceLocation
FROM pg_database
JOIN pg_authid on pg_database.datdba = pg_authid.oid
CROSS JOIN pg_tablespace
WHERE datistemplate = false
AND pg_database.dattablespace = pg_tablespace.oid;

where the function call is the last part of the SELECT statement. But I'm getting an error:

ERROR:  column "oid" does not exist
LINE 3:     pg_catalog.pg_tablespace_location(oid) as TableSpaceLoca...

when I try and run this and I'm not sure how to get the tablespace location as part of this query. If I run this function on it's own:

SELECT pg_catalog.pg_tablespace_location(oid) as "Location"
FROM pg_catalog.pg_tablespace

I get a proper response. Any ideas?

Answer 1

Please try this:

SELECT pg_database.datname as Database_Name, pg_authid.rolname as Database_Owner, 
pg_authid.rolsuper as IsSuperUser, pg_tablespace.spcname as TableSpaceName, 
pg_catalog.pg_tablespace_location(pg_tablespace.oid) as TableSpaceLocation
FROM pg_database
JOIN pg_authid on pg_database.datdba = pg_authid.oid
CROSS JOIN pg_tablespace
WHERE datistemplate = false
AND pg_database.dattablespace = pg_tablespace.oid;