Regex

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C4AB3E.CB4E60D0
Content-Type: text/plain


I know this is not a regex forum, however I had great assistance last time
and thought I would ask, since the regex is looking through an SQL
statement.

I was trying to parse a SQL statement and get all the tables used. I'm
actually doing this in Cold Fusion.

I have gone as far as this

(FROM | JOIN ).*(?<=INNER JOIN|LEFT JOIN|LEFT OUTER JOIN|AS|WHERE|ORDER BY)

I know I need to look for FROM or JOIN and get all text within INNER
JOIN,LEFT JOIN,LEFT OUTER JOIN,AS,WHERE,ORDER BY... there are various
others.

I have tried a forward reference however this does not work, I think it
needs to be between the .[^INNER JOIN|LEFT JOIN|LEFT OUT JOIN|AS|WHERE|ORDER
BY]* somehow!!!

Basically the regex should return TABLEA, TABLEB from:

Select *
from TABLEA
Inner jon TABLEB on tableb.columna = tablea.columna

Cheers,
Theo


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C4AB3E.CB4E60D0
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

I know this is not a regex forum, however =
I had great assistance last time and thought I would ask, since the regex i=
s looking through an SQL statement.

I was trying to parse a SQL statement and =
get all the tables used. I'm actually doing this in Cold Fusion.

I have gone as far as this

(FROM | JOIN ).*(?<=3DINNER JOIN|LEFT J=
OIN|LEFT OUTER JOIN|AS|WHERE|ORDER BY)

I know I need to look for FROM or JOIN and=
get all text within INNER JOIN,LEFT JOIN,LEFT OUTER JOIN,AS,WHERE,ORDER BY=
.... there are various others.

I have tried a forward reference however t=
his does not work, I think it needs to be between the .[^INNER JOIN|LEFT JO=
IN|LEFT OUT JOIN|AS|WHERE|ORDER BY]* somehow!!!

Basically the regex should return TABLEA, =
TABLEB from:

Select *

from TABLEA

Inner jon TABLEB on tableb.columna =3D ta=
blea.columna

Cheers,

        ">Theo

Re: Regex

Theo Galanakis writes:
> Basically the regex should return TABLEA, TABLEB from:

> Select *
> from TABLEA
> Inner jon TABLEB on tableb.columna = tablea.columna

You realize of course that this problem is mathematically impossible?
Regexes are less powerful than context-free grammars, and so it is a
certainty that there exist legal SQL statements that will fool any regex
that you invent for this purpose.

If you know that the SQL statements are coming from a query generator
that produces only a certain style of SQL code, then you might be able
to come up with a solution that works reliably for the output of that
query generator. But I wonder if you wouldn't be better off bypassing
the parse-and-deparse hacking and tapping directly into the query
generator.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Regex

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C4AB67.82B70470
Content-Type: text/plain


Thanks Tom,
I attacked the issue another way which appears to work...

I used :

explain select * from nodes left join node_names on
node_names.node_id = nodes.node_id

which returned :

query
QUERY PLAN
1 Merge Right Join (cost=429.16..793.48 rows=4510 width=193)
2 Merge Cond: ("outer".node_id = "inner".node_id)
3 -> Index Scan using node_names_node_id_key on node_names
(cost=0.00..278.58 rows= 7253 width=110)
4 -> Sort (cost=429.16..438.89 rows=3894 width=83)
5 Sort Key: nodes.node_id
6 -> Seq Scan on nodes (cost=0.00..196.94 rows=3894 width=83)

and then programatically searched for lines that begin with :

Seq Scan on #table_name#
Index Scan using #indexname# on #table_name#

obtaining the #table_name#

Being : nodes, node_names

Theo

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, 6 October 2004 1:36 PM
To: Theo Galanakis
Cc: Pgsql-Sql@Postgresql. Org
Subject: Re: [SQL] Regex


Theo Galanakis writes:
> Basically the regex should return TABLEA, TABLEB from:

> Select *
> from TABLEA
> Inner jon TABLEB on tableb.columna = tablea.columna

You realize of course that this problem is mathematically impossible?
Regexes are less powerful than context-free grammars, and so it is a
certainty that there exist legal SQL statements that will fool any regex
that you invent for this purpose.

If you know that the SQL statements are coming from a query generator that
produces only a certain style of SQL code, then you might be able to come up
with a solution that works reliably for the output of that query generator.
But I wonder if you wouldn't be better off bypassing the parse-and-deparse
hacking and tapping directly into the query generator.

regards, tom lane


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C4AB67.82B70470
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

Thanks Tom,

        I attacked th=
e issue another way which appears to work...

        I used :

        explain select=
* from nodes left join node_names on node_names.node_id =3D nodes.node_id<=
/FONT>