Обсуждение: question regarding regular expressions
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
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
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
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
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
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
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"