Re: parameters to pl/pgSQL functions
От | David Gardner |
---|---|
Тема | Re: parameters to pl/pgSQL functions |
Дата | |
Msg-id | 46815B71.9000906@gardnerit.net обсуждение исходный текст |
Ответ на | Re: parameters to pl/pgSQL functions (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-novice |
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 >
В списке pgsql-novice по дате отправления: