Abdul Mohammed <imonikemohammed@gmail.com> writes:
> Sorry for the late response and thank you Pavel for answering. This is my
> first exposure to pgsql, so please bear with me. I am still getting the
> Context message. Here is the modified function:
I tried this, and after creating a dummy "question" table I got
ERROR: structure of query does not match function result type
DETAIL: Returned type integer does not match expected type text in column 1.
CONTEXT: SQL statement "SELECT *
FROM crosstab('select sp.participant_id, distinct qu.text, sr.text
from survey_progress sp
join question qu
on sp.survey_id = qu.survey_id
join survey_response sr
on qu.id = sr.question_id
where qu.question_type_id = 8
order by 1,2')
AS final_result ("participant_id" integer,"what" text,"when" text,"who" text)"
PL/pgSQL function survey_ct() line 15 at RETURN QUERY
It's slightly surprising to me that this problem gets detected
before noticing that the embedded query is invalid (it's
syntactically incorrect because of the bogus placement of
"distinct", even before getting to the fact that I didn't
make a survey_progress table). Nonetheless, it's a real
problem with the way you're trying to use crosstab().
Given that the output of crosstab() will be an integer
column followed by some text columns, you can't just
"return query" in a calling function whose output is
specified to be a single text column.
Anyway, I'd make the following recommendations:
1. You need to get used to Postgres error message layout.
You're apparently focusing only on the last line of context,
which is about the least important part of the report.
2. I'd suggest debugging the base query before trying to
wrap it in a crosstab() call, and then testing the crosstab()
manually before you wrap it in a plpgsql function. That
would give you a better feeling for the work that
the plpgsql function has to do.
3. I kind of doubt that outputting a single text column
is really the end result you want here. How is it
meaningful to be crosstab'ing multiple questions if
that's to be the end result?
regards, tom lane