Many thanks for the explanations David. My failed attempts and your explanations have forced me to realise that I need to spend some time learning to walk before attempting to run.
Much regards On Fri, Nov 19, 2021 at 1:31 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed <imonikemoham...@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. >
