Thread: regex back-references?
Does postgresql support regex back-references? In case the term isn't
standard, what I'd like to do is match a string like
SELECT 'http://www.blakeley.com/foo/bar/biz.html'::text ~
'^http://.*([^\.]+\.[^\.]+)/?'::text;
except that I'm only interested in the part inside the parenthesis
(in case it's not obvious, I'm trying to extract the domain.tld, eg
"blakeley.com"). With Perl, one does this with code like:
$u = 'http://www.blakeley.com/foo/bar/biz.html';
if ($u =~ '^http://.*([^\.]+\.[^\.]+)/?') {
print "domain: $1\n";
}
Is this sort of thing supported in postgres?
Or is there another way to solve the problem? I got pretty close with:
SELECT trim(leading 'www.' from substring(url from 8 for
position('/' in trim(leading 'http://' from url))-1)) ...
but it's not quite right, since I want to to trim any hostname and
subdomains off of the URL (not just 'www.').
thanks,
-- Mike
Michael Blakeley <mike@blakeley.com> writes:
> Does postgresql support regex back-references?
There's no such function at the SQL level, AFAIR.
I'd recommend writing a function in either plperl or pltcl, according
to your taste. Both offer pretty much all the string-bashing
functionality you could possibly want ...
regards, tom lane
At 1:30 AM -0400 8/21/2000, Tom Lane wrote:
>Michael Blakeley <mike@blakeley.com> writes:
>> Does postgresql support regex back-references?
>
>There's no such function at the SQL level, AFAIR.
>
>I'd recommend writing a function in either plperl or pltcl, according
>to your taste. Both offer pretty much all the string-bashing
>functionality you could possibly want ...
Sounds like a plan - but I can't seem to create a function - can
anyone tell me what I'm doing wrong? A brief check of the archives
didn't turn up any hints...
Solaris 2.6, Postgresql 7.0.1, Perl 5.6, gcc 2.95.2.
After compiling and installing with
./configure --with-perl --without-tcl
psql=# CREATE FUNCTION pltest(VARCHAR) RETURNS VARCHAR AS
'return $_[0]' LANGUAGE 'plperl';
ERROR: Unrecognized language specified in a CREATE FUNCTION:
'plperl'. Recognized languages are sql, C, internal and the created
procedural languages.
Can I check for the presence of a .so somewhere? It seems to have
installed a couple in $PGSQL/lib:
-rwxr--r-- 1 root other 295784 Aug 21 12:47 plperl.so*
-rw-r--r-- 1 root other 690992 Aug 21 12:47 plpgsql.so
Also,
psql=# select * from pg_language;
lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
internal | f | f | 0 | n/a
C | f | f | 0 | /bin/cc
sql | f | f | 0 | postgres
(3 rows)
Do I need to tell postgres to rebuild pg_language, perhaps?
thanks,
-- Mike
Michael Blakeley <mike@blakeley.com> writes:
> Do I need to tell postgres to rebuild pg_language, perhaps?
See the createlang utility script. PL languages aren't installed
by default (due to possibly-overzealous concern about security).
regards, tom lane
At 4:26 PM -0400 8/21/2000, Tom Lane wrote: >Michael Blakeley <mike@blakeley.com> writes: >> Do I need to tell postgres to rebuild pg_language, perhaps? > >See the createlang utility script. PL languages aren't installed >by default (due to possibly-overzealous concern about security). Thanks - that did it. RFE: change the error message ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. to read ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'. Recognized languages are sql, C, internal and the created procedural languages. If you have compiled postgres with 'plperl' support, you may need to run the 'createlang' utility. thanks, -- Mike
I'm running postgresql 7.0.1 on Solaris 2.6, with the latest patch
cluster. I've compiled pg and Perl 5.6.0 with gcc 2.9.5-2.
I want to do some work with plperl fuctions, for text parsing. So I
run psql and do:
db=# CREATE FUNCTION testfunction(VARCHAR) RETURNS VARCHAR AS
db-# 'return "foo"' LANGUAGE 'plperl';
CREATE
db=# SELECT testfunction('foo');
...after a pause, I see:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#
truss-ing the backend process while this is happening doesn't show
much. Here it is, though:
9973/1: recv(6, 0x0017D8F0, 8192, 0) (sleeping...)
9973/1: recv(6, " Q S E L E C T t e s t".., 8192, 0) = 27
9973/1: time() = 968015516
9973/1: open("/usr/local/pgsql/data/base/db/pg_aggregate", O_RDWR)
= 35
9973/1: lseek(35, 0, SEEK_END) = 8192
9973/1: lseek(13, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: open("/usr/local/pgsql/data/base/db/pg_aggregate_name_type
_index", O_RDWR) = 36
9973/1: lseek(36, 0, SEEK_END) = 16384
9973/1: lseek(36, 0, SEEK_SET) = 0
9973/1: read(36, "\0\b1FF01FF0 \0\005 1 b".., 8192) = 8192
9973/1: lseek(36, 8192, SEEK_SET) = 8192
9973/1: read(36, "\0BC17801FF0 \0 ?8080 0".., 8192) = 8192
9973/1: lseek(35, 0, SEEK_END) = 8192
9973/1: lseek(35, 0, SEEK_SET) = 0
9973/1: read(35, "\0BC\r p \0 \0 ? 80 m".., 8192) = 8192
9973/1: lseek(13, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: open("/usr/local/pgsql/data/base/db/pg_proc_proname_narg_t
ype_index", O_RDWR) = 37
9973/1: lseek(37, 0, SEEK_END) = 229376
9973/1: lseek(37, 0, SEEK_SET) = 0
9973/1: read(37, "\0\b1FF01FF0 \0\005 1 b".., 8192) = 8192
9973/1: lseek(37, 24576, SEEK_SET) = 24576
9973/1: read(37, "\0 p14901FF0 \0 ?\080 p".., 8192) = 8192
9973/1: lseek(37, 139264, SEEK_SET) = 139264
9973/1: read(37, "\0C4\v `1FF0 \0 ?\080 p".., 8192) = 8192
9973/1: lseek(33, 204800, SEEK_SET) = 204800
9973/1: read(33, "\0A80198 \0 \0 >8080BD".., 8192) = 8192
9973/1: lseek(25, 8192, SEEK_SET) = 8192
9973/1: read(25, "01 01 ` \0 \0 ? 80 m".., 8192) = 8192
9973/1: lseek(34, 49152, SEEK_SET) = 49152
9973/1: read(34, "039011D01FF0 \0 ?C08010".., 8192) = 8192
9973/1: open("/usr/local/pgsql/data/base/db/pg_language", O_RDWR)
= 38
9973/1: lseek(38, 0, SEEK_END) = 8192
9973/1: lseek(13, 0, SEEK_END) = 8192
9973/1: lseek(15, 0, SEEK_END) = 8192
9973/1: lseek(16, 0, SEEK_END) = 16384
9973/1: open("/usr/local/pgsql/data/base/db/pg_language_oid_index"
, O_RDWR) = 39
9973/1: lseek(39, 0, SEEK_END) = 16384
9973/1: lseek(39, 0, SEEK_SET) = 0
9973/1: read(39, "\0\b1FF01FF0 \0\005 1 b".., 8192) = 8192
9973/1: lseek(39, 8192, SEEK_SET) = 8192
9973/1: read(39, "\0181FB01FF0 \0 ?C08010".., 8192) = 8192
9973/1: lseek(38, 0, SEEK_SET) = 0
9973/1: read(38, "\0181EA8 \0 \0 ? `80 O".., 8192) = 8192
9973/1: stat("/usr/local/pgsql/lib/plperl.so", 0xEFFFDD98) = 0
9973/1: open("/usr/local/pgsql/lib/plperl.so", O_RDONLY) = 40
9973/1: fstat(40, 0xEFFFDB7C) = 0
9973/1: mmap(0x00000000, 8192, PROT_READ|PROT_EXEC, MAP_PRIVATE, 40, 0)
= 0xEF590000
9973/1: mmap(0x00000000, 90112, PROT_READ|PROT_EXEC,
MAP_PRIVATE, 40, 0)
= 0xED3E0000
9973/1: mmap(0xED3F2000, 9060,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVA
TE|MAP_FIXED, 40, 8192) = 0xED3F2000
9973/1: munmap(0xED3E4000, 57344) = 0
9973/1: memcntl(0xED3E0000, 6604, MC_ADVISE, 0x0003, 0, 0) = 0
9973/1: close(40) = 0
9973/1:
open("/usr/local/lib/perl5/5.6.0/sun4-solaris/auto/Opcode/Opcode
.so", O_RDONLY) = 40
9973/1: fstat(40, 0xEFFFDAAC) = 0
9973/1: mmap(0xEF590000, 8192, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_FIXE
D, 40, 0) = 0xEF590000
9973/1: mmap(0x00000000, 90112, PROT_READ|PROT_EXEC,
MAP_PRIVATE, 40, 0)
= 0xED3C0000
9973/1: mmap(0xED3D4000, 4216,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVA
TE|MAP_FIXED, 40, 16384) = 0xED3D4000
9973/1: munmap(0xED3C6000, 57344) = 0
9973/1: memcntl(0xED3C0000, 6240, MC_ADVISE, 0x0003, 0, 0) = 0
9973/1: close(40) = 0
9973/1: open("/usr/lib/libperl.so", O_RDONLY) = 40
9973/1: fstat(40, 0xEFFFDAAC) = 0
9973/1: mmap(0xEF590000, 8192, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_FIXE
D, 40, 0) = 0xEF590000
9973/1: mmap(0x00000000, 1171456, PROT_READ|PROT_EXEC, MAP_PRIVATE, 40,
0) = 0xED280000
9973/1: mmap(0xED38E000, 56028,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIV
ATE|MAP_FIXED, 40, 1040384) = 0xED38E000
9973/1: open("/dev/zero", O_RDONLY) = 41
9973/1: mmap(0xED39C000, 1536,
PROT_READ|PROT_WRITE|PROT_EXEC, MAP_PRIVA
TE|MAP_FIXED, 41, 0) = 0xED39C000
9973/1: munmap(0xED380000, 57344) = 0
9973/1: memcntl(0xED280000, 136444, MC_ADVISE, 0x0003, 0, 0) = 0
9973/1: close(40) = 0
9973/1: mprotect(0xED3E0000, 15895, PROT_READ|PROT_WRITE|PROT_EXEC) = 0
9973/1: mprotect(0xED3E0000, 15895, PROT_READ|PROT_EXEC) = 0
9973/1: mprotect(0xED3C0000, 19335, PROT_READ|PROT_WRITE|PROT_EXEC) = 0
9973/1: mprotect(0xED3C0000, 19335, PROT_READ|PROT_EXEC) = 0
9973/1: mprotect(0xED280000, 1042504, PROT_READ|PROT_WRITE|PROT_EXEC) =
0
9973/1: mprotect(0xED280000, 1042504, PROT_READ|PROT_EXEC) = 0
9973/1: close(41) = 0
9973/1: munmap(0xEF590000, 8192) = 0
9973/1: brk(0x0039EBB8) = 0
9973/1: brk(0x003A0BB8) = 0
9973/1: getuid() = 101 [101]
9973/1: getuid() = 101 [101]
9973/1: getgid() = 100 [100]
9973/1: getgid() = 100 [100]
9973/1: time() = 968015516
9973/1: getcontext(0xEFFFDB50)
9973/1: Incurred fault #5, FLTACCESS %pc = 0xED2AB9E8
9973/1: siginfo: SIGBUS BUS_ADRALN addr=0x001F5045
9973/1: Received signal #10, SIGBUS [default]
9973/1: siginfo: SIGBUS BUS_ADRALN addr=0x001F5045
9973/1: *** process killed ***
The postgres log is also uninformative:
>Server process (pid 8307) exited with status 138 at Sun Sep 3 14:04:38 2000
>Terminating any active server processes...
>The Data Base System is in recovery mode
>NOTICE: Message from PostgreSQL backend:
> The Postmaster has informed me that some other backend died
>abnormally and possibly corrupted shared memory.
> I have rolled back the current transaction and am going to
>terminate your database system connection and exit.
> Please reconnect to the database system and repeat your query.
>Server processes were terminated at Sun Sep 3 14:04:38 2000
>Reinitializing shared memory and semaphores
>DEBUG: Data Base System is starting up at Sun Sep 3 14:04:38 2000
>DEBUG: Data Base System was interrupted being in production at Sun
>Sep 3 13:54:36 2000
>DEBUG: Data Base System is in production state at Sun Sep 3 14:04:38 2000
Any ideas?
thanks,
-- Mike
Michael Blakeley <mike@blakeley.com> writes:
> I'm running postgresql 7.0.1 on Solaris 2.6, with the latest patch
> cluster. I've compiled pg and Perl 5.6.0 with gcc 2.9.5-2.
Hmm. Your trace shows
> open("/usr/local/pgsql/lib/plperl.so", O_RDONLY) = 40
> open("/usr/local/lib/perl5/5.6.0/sun4-solaris/auto/Opcode/Opcode.so", O_RDONLY) = 40
> open("/usr/lib/libperl.so", O_RDONLY) = 40
I'd have expected libperl to be opened from somewhere in the same
general area as the Opcode module, say
/usr/local/lib/perl5/5.6.0/sun4-solaris/CORE/libperl.so
if the shape of the Perl install tree hasn't changed recently.
Perhaps you are pulling in some older, incompatible release of Perl
that came with your OS?
regards, tom lane
At 8:07 PM -0400 9/3/2000, Tom Lane wrote:
>Michael Blakeley <mike@blakeley.com> writes:
>> I'm running postgresql 7.0.1 on Solaris 2.6, with the latest patch
>> cluster. I've compiled pg and Perl 5.6.0 with gcc 2.9.5-2.
>
>Hmm. Your trace shows
>
>> open("/usr/local/pgsql/lib/plperl.so", O_RDONLY) = 40
>>
>>open("/usr/local/lib/perl5/5.6.0/sun4-solaris/auto/Opcode/Opcode.so",
>>O_RDONLY) = 40
>> open("/usr/lib/libperl.so", O_RDONLY) = 40
>
>I'd have expected libperl to be opened from somewhere in the same
>general area as the Opcode module, say
>/usr/local/lib/perl5/5.6.0/sun4-solaris/CORE/libperl.so
>if the shape of the Perl install tree hasn't changed recently.
>Perhaps you are pulling in some older, incompatible release of Perl
>that came with your OS?
No, the postgres backend was trying to load libperl.so from /usr/lib,
so I put a symbolic link there. It's all 5.6.0.
-- Mike
hi .. new user to postgresql access.. upon trying to create new database getting error as below: tcl error executing pg_exec create database handiman is not a valid postgresql connection well first off i'm not on network is that reason for error.. i haven't done much reading yet but just in case i wanted to post this for time sake if this error is mentioned in readme somewhere either in docs or website i'll find it and just ignore this otherwise any help appreciated...... lee ----------------