plpgsql keywords are hidden reserved words

I was somewhat bemused just now to find that this function stopped
working:

regression=# create function estimate_rows(query text) returns float8 as $$
declare r text;
begin
for r in execute 'explain ' || query loop
if substring(r from 'rows=[0-9]') is not null then
return substring (r from 'rows=([0-9]+)');
end if;
end loop;
return null;
end$$ language plpgsql strict;
CREATE FUNCTION
regression=# select estimate_rows('select * from tenk1 where unique1<500');
ERROR: column "query" does not exist
LINE 1: SELECT 'explain ' || query
^
QUERY: SELECT 'explain ' || query
CONTEXT: PL/pgSQL function "estimate_rows" line 3 at FOR over EXECUTE statement

This works fine in 8.2. The reason it no longer works is that "query"
is now a special token in the plpgsql lexer, and that means that it will
never be substituted for by read_sql_construct(). So it's effectively
a reserved word.

While I can work around this by changing the parameter name or using

for r in execute 'explain ' || estimate_rows.query loop

it's still a tad annoying, and it means that we have to be *very*
circumspect about adding new keywords to plpgsql.

I don't see any fix for this that's reasonable to try to shoehorn
into 8.3, but I think we really need to revisit the whole area of
plpgsql variable substitution during 8.4. We could make this problem
go away if variable substitution happened through a parser callback
instead of before parsing.

regards, tom lane

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

> I was somewhat bemused just now to find that this function stopped
> working:
>
> regression=# create function estimate_rows(query text) returns float8 as $$
...
> This works fine in 8.2. The reason it no longer works is that "query"
> is now a special token in the plpgsql lexer, and that means that it will
> never be substituted for by read_sql_construct(). So it's effectively
> a reserved word.

Perhaps we should be throwing a more intelligible error if you have a
parameter (or variable?) named in a way that will conflict?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> This works fine in 8.2. The reason it no longer works is that "query"
>> is now a special token in the plpgsql lexer, and that means that it will
>> never be substituted for by read_sql_construct(). So it's effectively
>> a reserved word.

> Perhaps we should be throwing a more intelligible error if you have a
> parameter (or variable?) named in a way that will conflict?

Actually, it seems you already do get such a complaint if you try to
use a keyword as a variable name:

ERROR: syntax error at or near "query"
LINE 2: declare query text;
^

Not the most tremendously helpful message, maybe, but at least it's
pointing at the right place. So the problem is only for function
parameter names, which aren't lexed by plpgsql itself but by the main
parser.

regards, tom lane

On Nov 5, 2007, at 10:20 AM, Tom Lane wrote:

> I don't see any fix for this that's reasonable to try to shoehorn
> into 8.3, but I think we really need to revisit the whole area of
> plpgsql variable substitution during 8.4. We could make this problem
> go away if variable substitution happened through a parser callback
> instead of before parsing.

Is there any feasibility to the idea of allowing pl/pgsql variables
and parameters to be prefixed with a special character like '$'? I'm
constantly adding prefixes like 'v_' because of conflicts with table
or column names. It would be nice to have something like "declare
$myvar integer;" so it would be very easy to distinguish variable and
parameter names from structure names or reserved words.

John DeSoi, Ph.D.

John DeSoi <desoi(at)pgedit(dot)com> writes:
> Is there any feasibility to the idea of allowing pl/pgsql variables
> and parameters to be prefixed with a special character like '$'?

I find this (a) really ugly, (b) incompatible with Oracle, which
you'll recall is one of the main driving ideas for plpgsql, and
(c) almost certainly a lexical conflict with dollar quoting.

Most other special characters you might suggest would create
parsing ambiguities too.

> I'm constantly adding prefixes like 'v_' because of conflicts with table
> or column names.

The reason you have to do that is that we got the lookup order backward:
per Oracle, column names within a query should bind more tightly than
plpgsql variable names, and if you need to disambiguate you qualify
the variables. We should fix that instead of bogotifying the syntax.

regards, tom lane

On Nov 5, 2007, at 11:58 AM, John DeSoi wrote:
> Is there any feasibility to the idea of allowing pl/pgsql variables
> and parameters to be prefixed with a special character like '$'?
> I'm constantly adding prefixes like 'v_' because of conflicts with
> table or column names. It would be nice to have something like
> "declare $myvar integer;" so it would be very easy to distinguish
> variable and parameter names from structure names or reserved words.

+528,382 ;)

At least in 8.3, IIRC you can safely use the name of the function to
refer to variables, but it would be very nice if you could just do
$blah in embedded SQL statements.

While we're talking about plpgsql... is there a TODO to allow RAISE
to take a variable instead of just a fixed string? Yes, I can always
do RAISE '%', variable, but then I lose % expansion.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828