Re: Querying database for table pk - better way?

Josh Trutwin wrote:

> > SELECT nspname as schema, c2.oid as tableoid, c2.relname as
> > table, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)
> > from E'\\((.*)\\)')

> 2.) Can you explain that substring line? What in the world is "from
> E'\\((.*)\\)')" doing? Somehow it gets the column name....

The function is
substring(foo from bar)
so the E'' stuff you see is just the second operand. The E'' syntax
allows backslash escapes; if you used '' without the E it would complain
about the backslashes, because the SQL standard requires them to not
mean escape characters, but in this case they are being treated as
escapes. The first pair of backslashes are there to protect the
parentheses from being turned them into capturing parens; they are
doubled because the SQL parser eats one of them, so the regex engine
really gets \((.*(\) which means "parens, then .*, then parens, but
capture whatever is matched by the .*".

Phew, I think this is a very confuse explanation. Hope it helps :-)

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Aprender sin pensar es inútil; pensar sin aprender, peligroso" (Confucio)