By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

regular expression searches

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 da***@fetter.org 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

Nov 23 '05 #2

P: n/a

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


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

Nov 23 '05 #3

P: n/a
"Kiarash Bodouhi" <kb******@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4

P: n/a
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/

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

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.