Postgres Pro
Downloads
Home   >   mailing lists

Thread: regular expression searches

regular expression searches

From
"David Bitner"
Date:
I am trying to create a PL/PGSQL function that can parse a street address
into the component parts (i.e. "200 W 54th Street" into num->200 dir->W
street->54th type->ST).

What I would like is to be able to use regular expressions within PL/PGSQL
to accomplish this using mapping tables for the different components.

For example, I would have a table with all the different acceptible road
types:
Abbreviation     |     Regex
__________________________________
RD            |    R(OA)?D
AV            |    AVE?(NUE)?
ST            |     STR?(EET)?

and replace everything that matches the regex with the abbreviation while
saving the road type as a variable.

Any help would be appreciated.

Thanks,
David

Re: regular expression searches

From
David Fetter
Date:
On Thu, Oct 07, 2004 at 04:07:08PM -0500, David Bitner wrote:
> I am trying to create a PL/PGSQL function that can parse a street address
> into the component parts (i.e. "200 W 54th Street" into num->200 dir->W
> street->54th type->ST).
>
> What I would like is to be able to use regular expressions within PL/PGSQL
> to accomplish this using mapping tables for the different components.

Perhaps PL/Perl or PL/Python would be more appropriate for this.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Question from a newbie

From
"Kiarash Bodouhi"
Date:
Hello All

I used to be MySQL user. I recently changed to PostGres. It is much more
fun. I have two questions. First, is it possible to call other functions
from plpython functions? I used following but didn't work. Any comments?

CREATE OR REPLACE FUNCTION test4()
  RETURNS "varchar" AS
'
return plpy.execute("select getcountrycode("9821788")",1)
'
  LANGUAGE 'plpythonu' VOLATILE;


Also, do you know any better way (apart from psql) to enter and test
functions? I used pgAdmin, but it didn't work properly with plpython. I
guess the indentation is not right and everytime I have to create the
function from psql in order to work. A little bit strange but it happened! I
don't know if I am doing something wrong or not but it seems no other person
complained.

Thanking you in advance
Regards
kia

Re: Question from a newbie

From
Tom Lane
Date:
"Kiarash Bodouhi" <kbodouhi@yahoo.com> writes:
> I have two questions. First, is it possible to call other functions
> from plpython functions? I used following but didn't work. Any comments?

> CREATE OR REPLACE FUNCTION test4()
>   RETURNS "varchar" AS
> '
> return plpy.execute("select getcountrycode("9821788")",1)
> '
>   LANGUAGE 'plpythonu' VOLATILE;

Didn't work how, exactly?  I don't know much Python but I'd think you
have a quote-nesting mistake there.  And anyway, shouldn't it be single
quotes in the SQL command, ie

    return plpy.execute("select getcountrycode('9821788')",1)

which you actually need to write as

    return plpy.execute("select getcountrycode(\'9821788\')",1)

because you're already inside a single-quoted string.

> Also, do you know any better way (apart from psql) to enter and test
> functions? I used pgAdmin, but it didn't work properly with plpython.

Dunno; it is certainly possible that pgAdmin isn't careful about
preserving leading indentation.  I'd suggest taking that up with the
pgAdmin guys; I'm sure they'll fix it when you point out that python
is picky about this.

            regards, tom lane

Re: Question from a newbie

From
Michael Fuhr
Date:
On Thu, Oct 07, 2004 at 10:55:03PM -0400, Tom Lane wrote:

>     return plpy.execute("select getcountrycode(\'9821788\')",1)

plpy.execute() returns a result object; querying a function that
returns a result object will probably look like this:

    test=> select test4();
                 test4
    --------------------------------
     <PLyResult object at 0x367140>
    (1 row)

The code should look more like this:

    result = plpy.execute("select getcountrycode(\'9821788\')",1)
    return result[0]["getcountrycode"]

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/