Unable to use crosstab in Postgres

Question

Postgres 9.2.1 on OSX 10.9.2.

If I run the following crosstab example query:

CREATE EXTENSION tablefunc; 

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

I get: ERROR: extension "tablefunc" already exists

But if I comment out CREATE EXTENSION

I get: ERROR: function crosstab(unknown) does not exist

How can I get out of this vicious circle? Is it a known issue?

Answer 1

the problem in my case was that the 'tablefunc' extension was defined on one specific schema in my DB, and not accessible to all schemas in it.

[edit: as explained above, 'not accessible to all schemas' should read 'cannot be loaded on all schemas']

I learned that:

1. the Extension can only be loaded into one schema - so load it into 'public'
2. you have to manually drop the extension from one schema before you can load it in another
3. you can list the loaded extensions per schema in pqsl using the command: \df *.crosstab

[edit: 4. you can access the extension either by search_path, by loading it on public schema or by explicitly specifying a schema]