Re: Execute command in PL/pgSQL function not executing

On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed <imonikemohammed@gmail.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.