Re: Querying database for table pk - better way? - Mailing list pgsql-general

From Alvaro Herrera
Subject Re: Querying database for table pk - better way?
Date
Msg-id 20070906032240.GR6102@alvh.no-ip.org
Whole thread Raw
In response to Re: Querying database for table pk - better way?  (Josh Trutwin <josh@trutwins.homeip.net>)
List pgsql-general
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)

pgsql-general by date:

Previous
From: Liam Slusser
Date:
Subject: Re: pg_dump doesn¹t dump everything?
Next
From: Ow Mun Heng
Date:
Subject: Re: Max File size per Table (1G limit??)