regular expression searches

[David Bitner]

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




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[David Fetter]

Re: regular expression searches

On Thu, Oct 07, 2004 at 04:07:08PM -0500, David Bitner wrote:[color=blue]
> 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.[/color]

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

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

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Kiarash Bodouhi]

Question from a newbie


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("s elect 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




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Re: Question from a newbie

"Kiarash Bodouhi" <kbodouhi@yahoo .com> writes:[color=blue]
> I have two questions. First, is it possible to call other functions
> from plpython functions? I used following but didn't work. Any comments?[/color]
[color=blue]
> CREATE OR REPLACE FUNCTION test4()
> RETURNS "varchar" AS
> '
> return plpy.execute("s elect getcountrycode( "9821788")" ,1)
> '
> LANGUAGE 'plpythonu' VOLATILE;[/color]

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("s elect getcountrycode( '9821788')",1)

which you actually need to write as

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

because you're already inside a single-quoted string.
[color=blue]
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Michael Fuhr]

Re: Question from a newbie

On Thu, Oct 07, 2004 at 10:55:03PM -0400, Tom Lane wrote:
[color=blue]
> return plpy.execute("s elect getcountrycode( \'9821788\')",1 )[/color]

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("s elect getcountrycode( \'9821788\')",1 )
return result[0]["getcountrycode "]

--
Michael Fuhr

http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings