Re: parameters to pl/pgSQL functions

[David Gardner <david(at)gardnerit(dot)net>]

Thanks,
I just had a huge "ahah!" moment. Because the table in question is new, 
it only has a few entries of test data in it, and there is only one 
entry of "LPFundID"=$1, so I didn't notice that it was evaluating $2=$2, 
and I just thought there was something goofy about the insert statement 
itself.
I have no problems renaming the parameter name to ntid_in.
> I think you're hoping that those double quotes prevent the names from
> being matched to the plpgsql variables, but this is not so.  "LPFundID"
> won't match lpfundid, but that's because of the case differential not
> the quotes. "ntid" does match ntid.  So that select is being interpreted
> as
> 	... WHERE "LPFundID" = $1 AND $2 = $2
> which is certainly not what you want; and the insert is failing outright
> because of $2 in the column name list.
>
> Moral: don't use variable names that are the same as table or field
> names you need to use in the same function.
>
> If you really need to do this, the correct solution is to qualify the
> field names, eg
> 	AND "NotificationLP".ntid = ntid
> plpgsql will never think that a dotted name matches a variable.  I fear
> that solution won't work for an INSERT column name list item though.
>
> 			regards, tom lane
>