Re: Support tab completion for upper character inputs in psql

I wrote:
> It's certainly arguable that the first case is right as-is and we
> shouldn't change it. I think that could be handled by tweaking my
> patch so that it wouldn't offer completions that start with a quote
> unless the input word does. That would also cause I<TAB> to complete
> immediately to id, which is arguably fine.

Here's a patch series that does it like that. I have to admit that
after playing with it, this is probably better. There's less
magic-looking behavior involved, and it lets me drop an ugly hack
I had to work around a case where Readline didn't want to play along.

0001 also cleans up one oversight in the previous version, which
is to beware of multibyte characters in parse_identifier(). I'm
not sure there is any actual hazard there, since we weren't looking
for backslashes, but it's better to be sure. I added the keyword
handling I'd left out before, too.

0002-0004 are largely as before.

I've also added 0005, which changes the prefix-matching clauses
in the SQL queries from "substring(foo,1,%d)='%s'" to
"foo LIKE '%s'". This simplifies reading the queries a little bit,
but the real reason to do it is that the planner can optimize the
catalog searches a lot better. It knows a lot about LIKE prefix
queries and exactly nothing about substring(). For example,
DROP TYPE foo<TAB> now produces a query like this:

explain SELECT t.typname, NULL::pg_catalog.text FROM pg_catalog.pg_type t WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND t.typname !~ '^_' AND (t.typname) LIKE 'foo%' AND pg_catalog.pg_type_is_visible(t.oid);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_type_typname_nsp_index on pg_type t (cost=0.28..16.63 rows=1 width=96)
Index Cond: ((typname >= 'foo'::text) AND (typname < 'fop'::text))
Filter: ((typname !~ '^_'::text) AND (typname ~~ 'foo%'::text) AND pg_type_is_visible(oid) AND ((typrelid = '0'::oid) OR (SubPlan 1)))
SubPlan 1
-> Index Scan using pg_class_oid_index on pg_class c (cost=0.28..8.30 rows=1 width=1)
Index Cond: (oid = t.typrelid)
(6 rows)

where before you got a seqscan:

explain SELECT pg_catalog.format_type(t.oid, NULL) FROM pg_catalog.pg_type t WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) AND t.typname !~ '^_' AND substring(pg_catalog.format_type(t.oid, NULL),1,3)='foo' AND pg_catalog.pg_type_is_visible(t.oid);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on pg_type t (cost=0.00..16691.86 rows=1 width=32)
Filter: ((typname !~ '^_'::text) AND ("substring"(format_type(oid, NULL::integer), 1, 3) = 'foo'::text) AND pg_type_is_visible(oid) AND ((typrelid = '0'::oid) OR (SubPlan 1)))
SubPlan 1
-> Index Scan using pg_class_oid_index on pg_class c (cost=0.28..8.30 rows=1 width=1)
Index Cond: (oid = t.typrelid)
(5 rows)

Again, while these queries only have to run at human speed, that doesn't
mean it's okay to be wasteful. I seem to recall hearing complaints that
they are noticeably slow in installations with many thousand tables, too.
This should help.

regards, tom lane