function crosstab(unknown, unknown) does not exist but it does

Question

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.

Answer 1

Need to run below query

Run \dx command .

if result like below need to run following query

 CREATE EXTENSION tablefunc;

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

Now you can run crosstab query it should be solved.