Postgres Pro
Downloads
Home   >   mailing lists

Thread: question regarding regular expressions

question regarding regular expressions

From
Cindy
Date:
If I've got something like "...AND citation ~ 'x[0-9]*'..." in my
query, is there any way to extract the portion that matched?  In
many languages if you surround with parentheses the part of the regexp
you're interested in, you can retrieve what was matched with $1, etc.

What I'm trying to do is given a possible citation value of arbitrary
letters/number sequences (eg a123b345c876), I would like to be able to
find the next sequence for a particular embedded sequence.  Let's say
I'm looking at a123b345, and I want to find the next b sequence (which
might be 346 or 347, or some number larger than 345) that I have,
ignoring the values of any of the c sequences (and d, and e, whatever
else is afterwards).

The problem I'm having is extracting (SUBSTR) the right number of
numerals in the match I've made for the ORDER BY clause, since the
value can be 0 to 9999 and substring either picks up all the
characters after the given start, or picks up a fixed number of chars
after the starting point.

Yes, I realize that citations shouldn't be a single varchar and should
instead be a set of fields but this is something I've inherited and I
would have to have a very good reason for changing this (and breaking
all kinds of things)...

My query at this point is something like this:

SELECT byteloc, citation FROM citations where
citation ~ '$prefix[0-9]*$postfix' AND
TO_NUMBER(SUBSTR(citation, $location), '999999') > $curcitefield
ORDER BY TO_NUMBER(SUBSTR(citation, $location), '999999') DESC LIMIT 1;

This only works for the very last letter/number field in the string, of
course.  If I want to find a letter/number field at the start or in
the middle of citation, I would have to add something to the substr
function to keep from picking up all the rest of the chars (some of which
would be non numeric) and that's where I got stuck.

Thoughts?

Thanks,
--Cindy
--
ctmoore@uci.edu

Re: question regarding regular expressions

From
Tom Lane
Date:
Cindy <ctmoore@uci.edu> writes:
> If I've got something like "...AND citation ~ 'x[0-9]*'..." in my
> query, is there any way to extract the portion that matched?

Not in SQL --- but you could easily write a plperl or pltcl function
to do that.

            regards, tom lane

Re: question regarding regular expressions

From
Cindy
Date:
Tom Lane writes:
 >Cindy <ctmoore@uci.edu> writes:
 >> If I've got something like "...AND citation ~ 'x[0-9]*'..." in my
 >> query, is there any way to extract the portion that matched?
 >
 >Not in SQL --- but you could easily write a plperl or pltcl function
 >to do that.

OK, I'm looking over the documentation for this.  One question,
which doesn't seem to be addressed in the documentation
(http://www.postgresql.org/idocs/index.php?plperl-install.html)
I'm looking at, it says that you must issue a
createlang plperl <dbname>
in order to make use of this in a particular database.  Does this
need to be done prior to populating the database, or can it be done
any time subsequent?

--Cindy
--
ctmoore@uci.edu

Re: question regarding regular expressions

From
Tino Wildenhain
Date:
Hi Cindy,

--On Montag, 16. September 2002 14:25 -0700 Cindy <ctmoore@uci.edu> wrote:

>
> Tom Lane writes:
>  >Cindy <ctmoore@uci.edu> writes:
>  >> If I've got something like "...AND citation ~ 'x[0-9]*'..." in my
>  >> query, is there any way to extract the portion that matched?
>  >
>  >Not in SQL --- but you could easily write a plperl or pltcl function
>  >to do that.
>
> OK, I'm looking over the documentation for this.  One question,
> which doesn't seem to be addressed in the documentation
> (http://www.postgresql.org/idocs/index.php?plperl-install.html)
> I'm looking at, it says that you must issue a
> createlang plperl <dbname>
> in order to make use of this in a particular database.  Does this
> need to be done prior to populating the database, or can it be done
> any time subsequent?

This can be done any time. And you dont have to stuck with perl,
even python (plpython) is a choice :)

Regards
Tino

Re: question regarding regular expressions

From
Cindy
Date:
Tino Wildenhain writes:

 >> I'm looking at, it says that you must issue a
 >> createlang plperl <dbname>
 >> in order to make use of this in a particular database.  Does this
 >> need to be done prior to populating the database, or can it be done
 >> any time subsequent?
 >
 >This can be done any time. And you dont have to stuck with perl,
 >even python (plpython) is a choice :)

OK.  I'm actually using 7.0.3 (it's supposed to be updated Real Soon Now),
and the documentation:
http://www.us.postgresql.org/users-lounge/docs/7.0/user/app-createlang.htm
doesn't seem to be helping:

Text=# createlang -l;
ERROR:  parser: parse error at or near "createlang"
Text=# createlang Text -l;
ERROR:  parser: parse error at or near "createlang"
Text=# createlang plperl;
ERROR:  parser: parse error at or near "createlang"
Text=# createlang 'plperl' Text;
ERROR:  parser: parse error at or near "createlang"
Text=# createlang plperl Text;
ERROR:  parser: parse error at or near "createlang"
Text=#

ack!

The options used for compiling this were (see below).

I also tried it on our 7.2 installation:

Text=# createlang plperl;
ERROR:  parser: parse error at or near "createlang"
Text=# createlang 'plperl';
ERROR:  parser: parse error at or near "createlang"
Text=# \q
[3:04pm] stephanus ~> psql -V
psql (PostgreSQL) 7.2
contains support for: readline, history, multibyte
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.

and

env CC='/opt/SUNWspro/bin/cc' CFLAGS='-v -fast' \
./configure                        \
--prefix=/usr/local/pg7.2          \
--with-includes=/usr/local/include \
--with-libs=/usr/local/lib         \
--enable-locale                    \
--enable-recode                    \
--enable-multibyte                 \
--with-maxbackends=256             \
--with-perl                        \
--with-pam                         \
--enable-odbc                      \
--with-iodbc                       \
--with-odbcinst=/usr/local/etc     \

as you can see, compiled with "with-perl" (for both 7.0.3, 7.2)


--
Cindy
ctmoore@uci.edu

Re: question regarding regular expressions

From
Tino Wildenhain
Date:
Hi Cindy,

--On Montag, 16. September 2002 15:07 -0700 Cindy <ctmoore@uci.edu> wrote:

>
> Tino Wildenhain writes:
>
>  >> I'm looking at, it says that you must issue a
>  >> createlang plperl <dbname>
>  >> in order to make use of this in a particular database.  Does this
>  >> need to be done prior to populating the database, or can it be done
>  >> any time subsequent?
>  >
>  >This can be done any time. And you dont have to stuck with perl,
>  >even python (plpython) is a choice :)
>
> OK.  I'm actually using 7.0.3 (it's supposed to be updated Real Soon Now),
> and the documentation:
> http://www.us.postgresql.org/users-lounge/docs/7.0/user/app-createlang.htm
> doesn't seem to be helping:
>
> Text=# createlang -l;
> ERROR:  parser: parse error at or near "createlang"
... erm. look at psql/bin! createlang is (like
psql, createdb, initdb, createuser etc) a shell command!

Hope that helps :)

PS: try to migrate to 7.2, it helps in many things. For example
you can turn on plpython with configure options rather then
having to patch it in.

Regards
Tino

Re: question regarding regular expressions

From
frbn
Date:
try:

frbn=# \h create language
Command:     CREATE LANGUAGE
Description: Defines a new language for functions
Syntax:
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE 'langname'
     HANDLER call_handler
     LANCOMPILER 'comment'

---
but,
"A handler must already exist for the language in question
when you use the CREATE LANGUAGE  command. " (cf online "practical postgresql")
find where your plpgsql.so lib is located (mine is in /usr/lib/pgsql):
---

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque
AS '/usr/lib/pgsql/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';

---

but the shell command "createlang" is easier to use.

Cindy wrote:
> Tino Wildenhain writes:
>
>  >> I'm looking at, it says that you must issue a
>  >> createlang plperl <dbname>
>  >> in order to make use of this in a particular database.  Does this
>  >> need to be done prior to populating the database, or can it be done
>  >> any time subsequent?
>  >
>  >This can be done any time. And you dont have to stuck with perl,
>  >even python (plpython) is a choice :)
>
> OK.  I'm actually using 7.0.3 (it's supposed to be updated Real Soon Now),
> and the documentation:
> http://www.us.postgresql.org/users-lounge/docs/7.0/user/app-createlang.htm
> doesn't seem to be helping:
>
> Text=# createlang -l;
> ERROR:  parser: parse error at or near "createlang"
> Text=# createlang Text -l;
> ERROR:  parser: parse error at or near "createlang"
> Text=# createlang plperl;
> ERROR:  parser: parse error at or near "createlang"
> Text=# createlang 'plperl' Text;
> ERROR:  parser: parse error at or near "createlang"
> Text=# createlang plperl Text;
> ERROR:  parser: parse error at or near "createlang"
> Text=#
>
> ack!
>
> The options used for compiling this were (see below).
>
> I also tried it on our 7.2 installation:
>
> Text=# createlang plperl;
> ERROR:  parser: parse error at or near "createlang"
> Text=# createlang 'plperl';
> ERROR:  parser: parse error at or near "createlang"