5

I have a crosstab function that I've used successfully many times in the past, but now it's dumping all the data at the end instead of pivoting it into the output table. It can't seem to find Crosstab. I've researched it doing the following;

  • create extension if not exists tablefunc; --- answer is: extension "tablefunc" already exists
  • create extension tablefunc with schema animals; answer is: as above
  • select count(*) from information_schema.routines where routine_name like 'crosstab%'; ---- answer is 6.

The following is a section of the function code: BEGIN str := '" " text,'; -- blanks in A1 cell

FOR rec IN SELECT DISTINCT col_name FROM an_in_tbl ORDER BY col_name LOOP str := str || '"' || rec.col_name || '" text' ||','; END LOOP;

str:= substring(str, 0, length(str));

EXECUTE 'CREATE EXTENSION IF NOT EXISTS tablefunc;
DROP TABLE IF EXISTS an_out_tbl;
CREATE TABLE an_out_tbl AS
SELECT *
FROM crosstab(''select row_name, col_name, row_value from an_in_tbl order by 1'',   
             ''SELECT DISTINCT col_name FROM an_in_tbl ORDER BY 1'')
     AS final_result ('|| str ||')';

select animal_pivot_fn()

NOTICE: extension "tablefunc" already exists, skipping NOTICE: table "an_out_tbl" does not exist, skipping ERROR: function crosstab(unknown, unknown) does not exist LINE 5: FROM crosstab('select row_name, col_name, row_value from... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: CREATE EXTENSION IF NOT EXISTS tablefunc; DROP TABLE IF EXISTS an_out_tbl; CREATE TABLE an_out_tbl AS SELECT * FROM crosstab('select row_name, col_name, row_value from an_in_tbl order by 1',
'SELECT DISTINCT col_name FROM an_in_tbl ORDER BY 1') AS final_result (" " text,"CAT" text,"DOG" text,"SNAKE" text,"HORSE" text,"ELEPHANT" text,"MOUSE" text,"MONKEY"... and many more... HERE IS WHERE THE DATA GETS DUMPED AND NO PIVOTED TABLE GETS CREATED.

giltdsur
  • 55
  • 1
  • 2
  • 7
  • 2
    It sounds like the schema where `tablefunc` is installed isn't in your [`search_path`](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH) – Nick Barnes Mar 18 '19 at 21:49
  • 1
    I went to the schema where this routine had previously worked and copy these functions: – giltdsur Mar 18 '19 at 22:34
  • 1
    I went to the schema where this routine had previously worked and copied these functions in: connectby (4 functions), crosstab (six functions). I don't know how these files got there. I don't think 'create extension if not exists tablefunc' is enough. I must have done something additional to get them into that old schema. So I just copied them into my new schema. Now I have other issues like tables can have up to 1600 columns, which I knew about, so I know that's not the case ... – giltdsur Mar 18 '19 at 22:42
  • 1
    It sounds like different clients are using different `search_path`s. Whoever ran the `CREATE EXTENSION` first would have installed the extension in their default schema, and so they can just call `crosstab()` without qualifying it. Clients without that schema in their `search_path` will need to call `schema_name.crosstab()` instead. The solution is to install it in a known location, and either make sure it's in everyone's `search_path`, or make sure everyone qualifies their `crosstab()` calls with a schema name. – Nick Barnes Mar 19 '19 at 08:33
  • 1
    Thank you, Nick. – giltdsur Mar 19 '19 at 18:42

1 Answers1

5

Need to run below query

Run \dx command .

enter image description here

if result like below need to run following query

 CREATE EXTENSION tablefunc;

enter image description here

Run \dx command again,result should be like below.

enter image description here

Now you can run crosstab query it should be solved.

Saranga kapilarathna
  • 564
  • 1
  • 12
  • 25