CODEWITHSUNDEEP
postgresqlpivotcrosstab
giltdsur
giltdsur

Reputation: 75

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

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;

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.

Upvotes: 7

Views: 17361

Answers (3)

Lulu
Lulu

Reputation: 1

I needed to specify my database when I start my postgres CLI:

psql <db_name>

And then,

CREATE EXTENSION tablefunc;

Upvotes: 0

Daniel L. VanDenBosch
Daniel L. VanDenBosch

Reputation: 2724

Is it possible you installed the tablefunc extension in a different schema?

This is what happened to me. I installed it in my sched schema by mistake

    SELECT 
        extname                 AS extname 
    ,   nspname                 AS schema 
    ,   extowner::regrole::text AS owner
    FROM pg_extension 
    INNER JOIN pg_namespace ON pg_namespace.oid = pg_extension.extnamespace
    WHERE extname = 'tablefunc';

I ran the following query above to find what schema it was in. I then changed by production code and prefixed crosstab to become sched.crosstab($$query 1$$,$$query 2$$) in my case.

Upvotes: 0

Saranga kapilarathna
Saranga kapilarathna

Reputation: 644

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.

Upvotes: 12

Related Questions