alvherre is currently certified at Master level.

Name: Alvaro Herrera
Member since: 2005-03-28 04:09:29
Last Login: 2010-05-13 14:53:37

FOAF RDF Share This



PostgreSQL developer

Recent blog entries by alvherre

Syndication: RSS 2.0

So there was an earthquake here and it seems some people tried to get news from me. I can't reach my mail currently, so pardon me while I abuse this blog for a bit. Fortunately my family is all alive and well; no damage taken. The building we live on is still standing and has no damages either, though our appartment is now a mess of broken dishes and bottles.

I will be obviously unable to work much on patches, or anything really, for at least a week ...

I just noticed that published an article on Josh Berkus' talk on, “How to destroy your community”. An interesting talk, judging from the article. Enjoy :-)

10 Sep 2009 (updated 10 Sep 2009 at 16:06 UTC) »
Update: This was supposed to be posted elsewhere. It seems Advogato lacks a way to delete posts, so I'm now forced to pester readers with some spanish junk.

Se acaba de publicar una nueva serie de versiones actualizadas de PostgreSQL para todas las ramas soportadas, desde 7.4 hasta 8.4. El anuncio en español apareció, entre otros, en (Y este es el anuncio oficial en la lista pgsql-announce). Se recomienda a todos actualizar a esta versión.

Y recuerden que para Windows sólo están soportadas las ramas 8.2, 8.3 y 8.4. En las nuevas versiones se corrigió finalmente el problema que daba lugar al error “could not reattach to shared memory”, así que los usuarios de Windows son los que estarán más contentos.


14 Jul 2009 (updated 14 Jul 2009 at 22:24 UTC) »

sprintf in SQL

Ever needed to print out something with embedded data? Not an uncommon requirement, I'm afraid. Most people just concatenate stuff together:

SELECT 'A number ' || 42 || ' is a ' || 21 || ' * ' || 2 || ' by any other name'

Eventually it turns out confusing, as it's easy to visually mix the concatenation operator || with the quotes.

PL/pgSQL offers a printf of sorts ... except that you can't use it everywhere you'd like:

RAISE NOTICE ' A number % is a % * % by any other name', 42, 21, 2;

How to capture such a thing so that you can, for instance, use that as a return value? Well, that's what most languages call an "sprintf" function or %-expansion. It's a trivial technique really.

So you want it in SQL? Well, here it is, as a PL/pgSQL function for maximum portability.

CREATE OR REPLACE FUNCTION printf(fmt text, variadic args anyarray) returns text
language plpgsql AS $$
argcnt int = 1;
chrcnt int = 0;
fmtlen int;
CHR text;
output text = '';
fmtlen = LENGTH(fmt);
chrcnt = chrcnt + 1;

-- ran out of format string? bail out
IF chrcnt > fmtlen THEN

-- grab our char
CHR = substring(fmt, chrcnt, 1);

-- %% means output a single %, and skip them
IF CHR = '%' AND substring(fmt, chrcnt + 1, 1) = '%' THEN
output = output || '%';
chrcnt = chrcnt + 1;

-- a % on its own means output an element from our arg list
output = output || COALESCE(args[argcnt]::text, '');
argcnt = argcnt + 1;

-- no special case? output the thing
output = output || CHR;

RETURN output;

This is not as flexible as the true sprintf function found in C and variants, which allow you to do neat stuff like fill some variables to fixed widths, or specify number of digits after the decimal point for non-integer values. But it's already quite useful. Furthermore, if you really need a given number of decimal you can add a cast to NUMERIC, which you can't do in C ...

The biggest problem it has is that any argument that's not implicitly castable to text must carry an explicit cast. The good thing is that by adding casts you can even print out complex structures like records or arrays.

# select printf('% is a % and % is a %, but %% is a %',
'hello', 'word', 1::text, 'number', 'percent sign');
hello is a word and 1 is a number, but % is a percent sign
(1 fila)

alvherre=# select printf('% %% is %',
ROW(1,2,3,'foo bar')::text);
3.01 % is (1,2,3,"foo bar")
(1 fila)

I'm sure you can find more interesting uses that than one!

I've uploaded this function to our growing collection of code snippets in our Wiki, so that it won't be lost in the dark corners of this blog.

My take on «Moderating Majordomo Lists with Vim and Mutt»


What I do instead is have these lines in .mutt/muttrc:

# Useful macros for Majordomo list administration
macro pager R "|~/bin/majordomo-reject\nd" "Majordomo reject"
macro pager A "|~/bin/majordomo-accept\nd" "Majordomo accept"

And then majordomo-accept and majordomo-reject are symlinks to a Perl program that looks like this:


use warnings; use Net::SMTP;

if ($0 =~ /accept/) { $action = "accept"; $fullaction = "Accepting"; } elsif ($0 =~ /reject/) { $action = "reject-quiet"; $fullaction = "Rejecting"; }

die "No mode defined" unless defined $action;

while (<>) { if (/^Subject: ([0-9A-Z-]{14}) : (CONSULT|REMINDER)/) { $token = $1; last; } }

die "No token defined" unless defined $token;

$smtp = Net::SMTP->new('localhost') or die "new: $!";

$smtp->mail(''); $smtp->to('');

$smtp->data; $smtp->datasend(<<END_OF_MAIL); From: Moderation Robot <alvherre\> To:'s Majordomo <majordomo\> Subject: $fullaction token $token

$action $token END_OF_MAIL $smtp->dataend(); $smtp->quit;

The main upside to this approach is that I don't have to hit "reply" to the email; I can just hit "R" or "A" on the mutt message view.

15 older entries...


alvherre certified others as follows:

  • alvherre certified alvherre as Journeyer
  • alvherre certified nconway as Master
  • alvherre certified gpoo as Master
  • alvherre certified olea as Master
  • alvherre certified mgonzalez as Apprentice

Others have certified alvherre as follows:

  • alvherre certified alvherre as Journeyer
  • nconway certified alvherre as Master
  • kjw certified alvherre as Journeyer
  • lerdsuwa certified alvherre as Journeyer
  • Zaitcev certified alvherre as Journeyer
  • gpoo certified alvherre as Master
  • olea certified alvherre as Master
  • mgonzalez certified alvherre as Master
  • nixnut certified alvherre as Master
  • thecodekeeper certified alvherre as Master

[ Certification disabled because you're not logged in. ]

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!

Share this page