Thread: How can I use crosstab functons in PostgreSQL 9.3?
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
Rob Richardson <RDRichardson@rad-con.com> writes:
> I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I
geterrors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its
methodsalready exist.
This looks like a search_path problem. You could try "\dx+ tablefunc"
to see which schema its functions are in, then adjust your search_path
to include that, or else schema-qualify the function names.
regards, tom lane
Looks to me like argument types possibly? The article creates various combinations of crosstab() function but you are passing in a query. Wrap your query in quotes (and then escape those within it). Then you'll be passing in a "text" type not an "unknown" as the error clearly shows. Tim Clarke On 15/10/15 15:19, Tom Lane wrote: > Rob Richardson <RDRichardson@rad-con.com> writes: >> I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. Iget errors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its methodsalready exist. > This looks like a search_path problem. You could try "\dx+ tablefunc" > to see which schema its functions are in, then adjust your search_path > to include that, or else schema-qualify the function names. > > regards, tom lane > >
Tim, Thank you, but I think I already did that. The query is a dollar-quoted string, so there should be no need to do anythingwith the single quote marks within it, so I would have thought the query engine would already know that it's text. But after seeing the first error message, I explicitly casted it using "::text". The error message that time saidthat crosstab(text) was not found, so that doesn't seem to be the problem. RobR -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Clarke Sent: Thursday, October 15, 2015 10:31 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3? Looks to me like argument types possibly? The article creates various combinations of crosstab() function but you are passingin a query. Wrap your query in quotes (and then escape those within it). Then you'll be passing in a "text" type notan "unknown" as the error clearly shows. Tim Clarke
I should have mentioned (twice now) that I'm running under Windows 7.
RobR
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 15, 2015 10:19 AM
To: Rob Richardson
Cc: pgsql-general General
Subject: Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?
Rob Richardson <RDRichardson@rad-con.com> writes:
> I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I
geterrors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its
methodsalready exist.
This looks like a search_path problem. You could try "\dx+ tablefunc"
to see which schema its functions are in, then adjust your search_path to include that, or else schema-qualify the
functionnames.
regards, tom lane
By George, I think I've got it! When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the crosstab methods and my sample query worked. RobR
By George, I think I've got it!
When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the crosstab methods and my sample query worked.