Search Postgresql Archives

Re: Execute command in PL/pgSQL function not executing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux