imitatio creatio

hstore is a “Lazy DBA” extension for postgres, which enables storing hash tables in a table column.

When using hstore, sometimes you want to rename hash keys.


#
# create function renamekey( _hstore hstore, _oldname text, _newname text ) returns hstore as
$$ select delete($1, $2) || ($3 => ($1 -> $2)) $$ language sql immutable strict;
#
# \set old_codename '''dizzyflag'''
# \set new_codename '''is_trade'''
#
# UPDATE table_with_hstore
SET flags = renamekey(flags, :old_codename, :new_codename)
WHERE (flags -> :old_codename) is not null;
#


perlre says it does not support lookbehind matches with arbitrary length.

here is a workaround


#!perl -l -w
use strict;
#
print my $txt = 'Alice has a fish. It is a nice fish.
Bob has a dog. John has a cat.
Line 3 is not important.
Filip has a perl.
---------------------------';
#
=pod WE WANT THIS but this gives Perl error.
while ( $txt =~ m{(?<=$behind_re)($match_re)(?=$ahead_re)}g ) {
print $&;
}
=cut
#
my $behind_re = qr/[A-Z][a-z]+ has a /; # Filip has a
my $match_re = qr/\w+/i; # perl
my $ahead_re = qr/\./; # .
#
while ( $txt =~ m{($behind_re)}g ) { # we look for behind match globally,
if ( $txt =~ m{\G($match_re)(?=$ahead_re)} ) { # we search for the rest anchored at each found position
print $&; # voila
}
}
#


Jarek napisał ciekawy artykuł o sortowaniu napisów w PostgreSQL (i nie tylko, w zasadzie rzecz dotyczy localesów w glibc).