Re: ERROR: variable not found in subplan target lists

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>> Tom Lane wrote:
>>> such a hokey query (how many applications really write "WHERE false"?),
>
>> Granted this isn't WHERE FALSE, but I certainly see WHERE TRUE all the
>> time in similar scenarios and I have seen WHERE FALSE.
>
> In what context? Either "WHERE TRUE AND ..." or "WHERE FALSE OR ..."
> strike me as perfectly sensible, but "WHERE FALSE AND ..." doesn't
> seem to have obvious usefulness.

I frequently wrote queries like

WHERE (?=1 OR col1 LIKE ?)
AND (?=1 OR col2 LIKE ?)

and then substituted parameters in pairs, one to indicate if a search on a
column was necessary and a second to pass the search parameter. That's easier
than building up a query with whole sections which might disappear. It's also
safer since it's easier to see that the query doesn't have any unquoted
strings interpolated into it.

Now, as long as the driver's using bound parameters it won't hit this issue
but if the queries were complex it might have made sense to have the driver do
the variable substitution and execute the query without parameters. Also, if
in the future we do handle multiple prepared plans for prepared statements it
would run into this kind of problem.

But then even in that case your solution still works. I'm just mentioning that
there are indeed queries like this out there.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com