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
