Re: Problem with Crosstab (Concatenate Problem)

On 11/1/10 11:54 PM, Stefan Schwarzer wrote:
>> SELECT * FROM
>> crosstab(
>> 'SELECT name, year_start, value FROM foo ORDER BY 1',
>> 'SELECT DISTINCT year_start FROM foo'
>> )
>> AS ct(name varchar, y_2010 float8, y_2011 float8);

> Hi Joe. Thanks a lot for the suggestions. Tried it out, but same error message:
>
> ERROR: invalid input syntax for integer: "SELECT DISTINCT year_start FROM foo"
> LINE 4: 'SELECT DISTINCT year_start FROM foo'
> ^
>
> Did this work for you? Then this would indeed be strange.
>
> I wonder if the second crosstab SQL must have the same column names as the final output or not ("2010" vs. "y_2010").
>
> Anyone can help me out? Thanks a lot for any tips!

What version of PostgreSQL are you running? The error seems to indicate
that you don't have the crosstab(text,text) form of the function.

In psql do:
contrib_regression=# \df crosstab
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------+------------------+---------------------+--------
public | crosstab | SETOF record | text | normal
public | crosstab | SETOF record | text, integer | normal
public | crosstab | SETOF record | text, text | normal
(3 rows)

Does it look like this?

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support