Re: [GENERAL] Count(*) throws error

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> On Wed, 2007-07-11 at 18:13 -0400, Tom Lane wrote:
>> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
>>> Seems like we could be slightly more friendly without too much bother:
>>> at least only substitute after the VALUES clause in INSERT.
>>
>> Surely you jest.

> No. There are a places where parameters clearly aren't allowed, so
> making the substitutions in those places can easily be prevented. The
> remainder of the problem is as hard as you think, but getting half way
> there seems very easy.

It's not nearly as easy as you think. Even for the limited case of
the column list for an INSERT, consider

DECLARE i int; j int;
...
INSERT INTO mytable (arraycol[i]) VALUES (j);

Considering that the current plpgsql parser doesn't have any knowledge
*at all* of the syntactic structure of individual SQL commands, even
teaching it to recognize an INSERT column list correctly would be a huge
addition of code. The payback for that seems darn small considering
that (1) that's only a small portion of the trouble cases and (2) it is
very easy for users to recognize a failure in this context, whereas many
of the other trouble cases aren't mechanically recognizable at all.

Ultimately we have to do a better job of teaching users to recognize
this issue for themselves; which is why I think it's basically a
documentation problem.

The other problem with trying to inject a small amount of smarts is that
it complicates explaining the system. I think the docs have to explain
*exactly* how the substitution works (which they fail to do right now,
but I intend to make 'em do so today), and putting in any special cases
will make that explanation longer and more confusing.

regards, tom lane