Re: Execute command in PL/pgSQL function not executing

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>