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