regular expression pattern to find table names in most PostgreSQL(-like) queries

table_re.py

import re

# this pattern should pick out tablenames or schema.tablenames in 
# most contexts, respecting legal names, including double-quoted table names 
# with otherwise illegal characters.
# known exceptions include GRANT statements and "joins" using multiple tables
# in FROM with WHERE conditions.

pattern = r'(?:from|join|(?:create|alter|drop|truncate|update)' \
           '(?:\s*table|\s*view|\s*)(?:\sas)?)(?:\s+)' \
           '((?:[\w_0-9]+|".*?")(?:\.(?:[\w_0-9]+|".*?"))?)'

queries = '''select * from "foo"."bar" AS t1 \
left join foo.baz AS t2 on t1.qux = t2.quux; \
select foo from bar.baz; truncate foo.bar; \
truncate table baz.quux; \
alter table foo.bar add column norf varchar; \
create view bar.qux as (select * from foo.bar); \
select foo from (select foo from subbar.qux); \
select foo from bar; \
select bar from "baz-qux".quux; \
update table baz.qux set norf='xyzzy' where quux in \
(select quux from norf.qux);'''

print re.findall(pattern, queries)

# ['"foo"."bar"', 'foo.baz', 'bar.baz', 'foo.bar', 
#  'baz.quux', 'foo.bar', 'bar.qux', 'foo.bar', 'subbar.qux',
#  'bar', '"baz-qux".quux', 'baz.qux', 'norf.qux']