I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I get errors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its methods already exist.
For example, I am trying to run the code contained on this page: https://learnerspeak.wordpress.com/2012/09/02/97/ . After adjusting quotation marks, my crosstab query from that example is:
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);
That query gives me the following error message:
ERROR: function crosstab(unknown) does not exist
LINE 2: FROM crosstab(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function crosstab(unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 15
I don’t know why it thinks the argument’s type is unknown. But if I explicitly cast it to text, I get:
ERROR: function crosstab(text) does not exist
LINE 2: FROM crosstab(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function crosstab(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 15
Thank you for your help.
RobR