Re: [GENERAL] Count(*) throws error

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

> "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);

huh, i hadn't actually seen that before -- it doesn't seem very useful. But
I've certainly seen and used "UPDATE SET arraycol[i] = val".

> 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.

It does eventually call the SQL parser though doesn't it? I was thinking the
way to do it would be to delay substituting the formal parameters to later,
after the parsing.

So instead of substituting them as the tokens are lexed, instead suck in the
tokens, run the parser -- which we currently do anyways just to check the
syntax -- then walk the tree looking for ColumnRefs where the name matches a
variable name. Then keep around that parse tree instead of just the series of
lex tokens to later call analyze on and execute.

> The other problem with trying to inject a small amount of smarts is that
> it complicates explaining the system.

Well that's quite true, but then that's the basic difficulty with any part of
plpgsql.

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