Re: Execute command in PL/pgSQL function not executing

On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed <imonikemohammed(at)gmail(dot)com>
wrote:

> The second part tries to use the string to build a pivot table using the
> crosstab function.
>

Aside from the simple learning curve on how to write functions in
PostgreSQL you also are dealing with the fact that you are dealing with a
query that has a variable number of columns and that is just not something
that PostgreSQL allows. It is in fact the reason the crosstab function
itself has to use the convoluted record return syntax where the caller has
to declare how many columns the function is going to return. You are
trying to get around this by having the function scan the table at runtime
to figure out which columns it needs to declare. But that won't work
because the function itself still has to know how many columns it is going
to output when it is called.

Personally I've found two solutions to this. Do the dynamic part in
application code and just send the dynamic SQL to the server for
execution. Or, turn your output into a container type (I've used CSV in
the past but JSON probably works better) and just return the complex value
from the function; then the application just has to deal with a simple
decomposing of the complex value into the final table it represents.

David J.