Re: Execute command in PL/pgSQL function not executing

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