Thursday, November 25, 2004

Perl 6 nightmare

Perl afficionados will no doubt be frightened by the cover of the new Perl 6 book:

The new Perl 6 book cover

(found on


Monday, November 22, 2004

Baghdad Year Zero

Baghdad Year Zero: "The Bush Administration did have a plan for what it would do after the war; put simply, it was to lay out as much honey as possible, then sit back and wait for the flies."

Un article passionnant de Naomi Klein, publié dans Harper's Magazine, sur l'utilisation de l'Irak comme terrain d'expérimentation de théories économiques néolibérales. (C'est en anglais). Autre lien vers le même article ici, et une interview à ce propos sur, y compris audio, vidéo, et transcription.

Wednesday, November 17, 2004

Postgresql: list users and groups

Users and groups are stored in 2 different tables (in views, really, I think): pg_user and pg_group. For some reason, I had a hard time today to find how to combine them.

This post suggesting

SELECT groname,usename from pg_group,pg_user where usesysid = any(grolist);

looked promising, but it didn't work for me. Probably requires a later Postgresql version.

pg_group lists the user id's in each group. Besides being id's and not names, the problem is that they are in an array field. To get a list of groups with the users they contain, or a list of users with the groups they are a member of, this is what I needed with my postgres 7.2:

Array operators

We need the array operator *= which may not be installed by default. It can be found in the contrib directory of the source.

With this, we can write things like

SELECT groname from pg_group WHERE grolist *= 100;

to list group names of which user 100 is a member.

List aggregate

then we need a function and an aggregate:

CREATE OR REPLACE FUNCTION comma_cat(text, text) RETURNS text AS
WHEN $2 is null OR $2 = '''' THEN $1
WHEN $1 is null OR $1 = '''' THEN $2
ELSE $1 || '', '' || $2


Now we can do things like:

SELECT g.groname, list(u.usename) FROM pg_user u, pg_group g
WHERE g.grolist *= u.usesysid GROUP BY g.groname;

SELECT u.usename, list(g.groname) FROM pg_user u, pg_group g
WHERE g.grolist *= u.usesysid GROUP BY u.usename;