Re: Execute command in PL/pgSQL function not executing

Thank you very much for your response Tom. I will follow your
recommendations. As for the 3rd one, I am actually trying to output the
pivot table I am trying to create using crosstab. I read somewhere that you
could have a table as a return type. I, therefore tried using the pivot
table alias as the return type but got an error along lines of the alias
not being recognized.

Much regards

On Fri, Nov 19, 2021 at 12:59 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Abdul Mohammed <imonikemohammed(at)gmail(dot)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
>