X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4366C32A009 for ; Wed, 6 Oct 2004 06:48:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75935-05 for ; Wed, 6 Oct 2004 05:48:12 +0000 (GMT) Received: from angel.lonelyplanet.com.au (mail.lonelyplanet.com.au [202.147.44.168]) by svr1.postgresql.org (Postfix) with ESMTP id 617AA329F8D for ; Wed, 6 Oct 2004 06:48:11 +0100 (BST) Received: from shiva.au.lpint.net ([192.168.61.22]) by angel with InterScan Messaging Security Suite; Wed, 06 Oct 2004 15:43:56 +1000 Received: by shiva.au.lpint.net with Internet Mail Service (5.5.2656.59) id <4G14KNDM>; Wed, 6 Oct 2004 15:44:17 +1000 Message-ID: <82E30406384FFB44AFD1012BAB230B5505F40D29@shiva.au.lpint.net> From: Theo Galanakis To: "Pgsql-Sql@Postgresql. Org" Cc: 'Tom Lane' Subject: Re: Regex Date: Wed, 6 Oct 2004 15:44:13 +1000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2656.59) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C4AB67.82B70470" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.0 tagged_above=0.0 required=5.0 tests=DNS_FROM_RFCI_DSN, EXCUSE_16, HTML_20_30, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200410/37 X-Sequence-Number: 19140 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 RE: [SQL] Regex

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>

        which returned= :

        query
          QUERY = PLAN
        1 Merge Right= Join (cost=3D429.16..793.48 rows=3D4510 width=3D193)
        2 Merge Cond:= ("outer".node_id =3D "inner".node_id)
        3 -> Index= Scan using node_names_node_id_key on node_names (cost=3D0.00..278.58 rows= =3D             &nbs= p;       7253 width=3D110)

        4 -> Sort (= cost=3D429.16..438.89 rows=3D3894 width=3D83)
        5 Sort Key: n= odes.node_id
        6 -> Seq S= can on nodes (cost=3D0.00..196.94 rows=3D3894 width=3D83)
       =0D
        and then prog= ramatically searched for lines that begin with :

        Seq Scan on #t= able_name#
        Index Scan us= ing #indexname# on #table_name#

        obtaining the = #table_name#

        Being : nodes,= node_names

Theo

-----Original Message-----
From: Tom Lane [mai= lto: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 <Theo.Galanakis@lonelyplanet.com.au>= writes:
> Basically the regex should return TABLEA, TABLEB fr= om:

> Select *
> from TABLEA
> Inner jon TABLEB on tableb.columna =3D tablea.colum= na

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

If you know that the SQL statements are coming from a que= ry 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 t= hat query generator.  But I wonder if you wouldn't be better off bypas= sing the parse-and-deparse hacking and tapping directly into the query gene= rator.

             = ;           reg= ards, 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 in= tended recipient of this email, you must not
use, copy or disclose its c= ontent to anyone. You must not copy or
communicate to others content t= hat is confidential or subject to
copyright, unless you have the consen= t of the content owner.
= ------_=_NextPart_001_01C4AB67.82B70470--