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).

Same as depesz and Greg, but using user-defined function:


CREATE FUNCTION ea_totals(text) RETURNS text AS $body$
my $result;
my $com = shift;
$com =~ /^\s*explain\s+/i or die qq{Not an explain query\n};
my $rv = spi_exec_query($com);
$rv->{status} eq 'SPI_OK_UTILITY' or die qq{Not a proper explain?\n};
for (map { $_->{'QUERY PLAN'} } @{$rv->{rows}}) {
my $string = ' ' x 10;
if ( /actual time=\d+\.\d*\.\.(\d+\.\d*) rows=\d+ loops=(\d+)/ ) {
$string = sprintf("%10.1f", $1 * $2);
}
$result .= "$string $_ \n";
}
return $result;
$body$ LANGUAGE plperl;


Usage:

SELECT * FROM ea_totals( $$ explain analyze query here $$);


Task: convert legal UTF8 into Windows native unicode (lilttle-endian, UTF-16).

Solution: UTF8ToUnicode.pl

This way you will identical file as if you opened input with Notepad and saved as “Unicode”.