Tag: example

How can I send mail or HTTP request from database?

This question happens every now and then in one of PostgreSQL support places.

Whenever it happens, I just suggest to not try, as interacting with outside world from database can be problematic, and instead use LISTEN/NOTIFY.

But it occurred to me, that while I know how to do it, I don't think I actually did it. It being: handle listen/notify in real life code. So let's try. I will not be writing actual email sending or http requesting, but will make sure that my program will get, from database, information when NOTIFY happens. How hard could it be?

Continue reading How can I send mail or HTTP request from database?

Filling the gaps with window functions

Couple of days ago I had a problem that I couldn't solve after ~ 2 hours, and decided to ask on IRC. Almost immediately after asking, I figured out the solution, but David asked me to write about the solution, even though it's now (for me) completely obvious.

The problem was like this:

I had two tables, with very simple structure: event_when timestamptz, event_count int4, and wanted to show it as a single recordset with columns: event_when, event_count_a, event_count_b, but the problem was that event_when usually didn't match. Here is an example:

Continue reading Filling the gaps with window functions

Getting random interfacelift wallpaper

Just thought I'll share it – maybe somebody else will use it.

I'm using KDE 3.5, so the final “dcop …" command, simply sets the file as current wallpaper.

I also use 1680×1050 resolution – which you can probably guess from the code 🙂

#!/usr/bin/perl -w
use strict;
use English qw( -no_match_vars );
use WWW::Mechanize;
 
my $agent = WWW::Mechanize->new();
 
$agent->get("http://interfacelift.com/wallpaper_beta/downloads/random/widescreen/1680x1050/");
exit unless 200 == $agent->res->code;
 
my @links = $agent->find_all_links(
    'url_regex' => qr{1680x1050.jpg},
);
exit if 0 == scalar @links;
 
$agent->get( $links[ rand @links ] );
exit unless 200 == $agent->res->code;
 
my $username = getpwuid( $REAL_USER_ID );
my $filename = '/tmp/interfacelift.' . $username . '.jpg';
 
open my $fh, '>', $filename or exit;
binmode $fh;
print $fh $agent->res->decoded_content;
close $fh;
 
$ENV{"DISPLAY"} = ':0';
system( qw( /usr/bin/dcop kdesktop KBackgroundIface setWallpaper ), $filename, 4 );
 
exit;

Pretty simplistic, but it does the job. Now, I add this line to my crontab:

*/5  * * * * /home/depesz/bin/interfacelift.pl

And enjoy new, nice wallpaper every 5 minutes 🙂