13

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Black
  • 5,023
  • 6
  • 63
  • 92

3 Answers3

14

You can change the first line into:

CREATE EXTENSION IF NOT EXISTS tablefunc;
akbarbin
  • 4,985
  • 1
  • 28
  • 31
9

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]

Black
  • 5,023
  • 6
  • 63
  • 92
  • Note that the `public` schema is just another schema. Needs to be in the `search_path` just like any other if you want to access it by default. – Erwin Brandstetter Apr 15 '14 at 23:33
5

There's a misconception in your answer:

and not accessible to all schemas in it.

All schemas inside the same database are accessible to all sessions in that same database, (as long as privileges are given). It's a matter of setting the search_path. Schemas work much like directories / folders in the file system.

Alternatively, you can schema-qualify the function (and even operators) to access it independently of the search_path:

SELECT *
FROM my_extension_schema.crosstab(
    $$select rowid, attribute, "value"
      from   ct
      where  attribute IN ('att2', 'att3')
      order  by 1,2$$
   ,$$VALUES ('att2'), ('att3')$$
   ) AS ct(row_name text, category_2 text, category_3 text);

More:

Dubious crosstab()

Your query returned attributes 'att2' and 'att3', but the column definition list had three categories (category_1, category_2, category_3) that do not match the query.
I removed category_1 and added the second parameter to crosstab() - the "safe" version. More details here:

Aside:
Don't use value as column name, even if Postgres allows it. It's a reserved word in standard SQL.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • the (erroneous?) query came straight from the Docs http://www.postgresql.org/docs/9.1/static/tablefunc.html – Black Apr 15 '14 at 23:26
  • @Francis: Well, that example tries to demonstrates a *shortcoming* of the `crosstab()` function with one parameter. As you can see in the result there `att2` ends up in `category1`, `att3` in `category2` and `category3` remains empty. Probably not what one would normally intend ... – Erwin Brandstetter Apr 15 '14 at 23:31
  • fair enough. I was only using it to illustrate the curious absence of the tablefunc extension – Black Apr 15 '14 at 23:34