Perl 6 nightmare

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

(found on http://bleaklow.com/blog/archive/000018.html)

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 democracynow.org, y compris audio, vidéo, et transcription.

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
'SELECT CASE
 WHEN $2 is null OR $2 = '''' THEN $1
 WHEN $1 is null OR $1 = '''' THEN $2
 ELSE $1 || '', '' || $2
END'
LANGUAGE 'sql';

DROP AGGREGATE list text;
CREATE AGGREGATE list(
 sfunc1=comma_cat,
 basetype=text,
 stype1=text,
 initcond1=''
);

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;

etc.