Re: Planning error in dynamic string creation in plpgsql

On 01/05/2014 08:23 PM, Keith Fiske wrote:
> That fixed it! In the example and my original as well. Thank you very much!
>
> And wow that was incredibly misleading where the cast was supposed to go
> going by the error given and when it was thrown. That EXECUTE statement
> works perfectly fine, seeing as the v_record variable got its assignment
> with no issue.
>
> Any chance you can explain what's going on here? Never would've thought
> to put the cast there to fix the problem.

No absolute answer, just educated guesses. One, the EXECUTE caused the
v_record to be dynamically planned for each pass so it dealt with the
type change. The assignment to v_sql in the FOREACH was planned the
first time through. On the second pass when the type changed it choked.

Two from here:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

' If the expression's result data type doesn't match the variable's data
type, or the variable has a specific size/precision (like char(20)), the
result value will be implicitly converted by the PL/pgSQL interpreter
using the result type's output-function and the variable type's
input-function. Note that this could potentially result in run-time
errors generated by the input function, if the string form of the result
value is not acceptable to the input function.'

Led to the hunch that casting to text in the query would be the key.

>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com