From: James Cloos <cloos@jhcloos.com>
To: pgsql-sql@postgresql.org
Subject: counting related rows
User-Agent: Gnus/5.110011 (No Gnus v0.11) Emacs/24.0.50 (gnu/linux)
Face: iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAABHNCSVQICAgIfAhkiAAAAI1J
	REFUOE+lU9ESgCAIg64P1y+ngUdxhl5H8wFbbM0OmUiEhKkCYaZThXCo6KE5sCbA1DDX3genvO4d
	eBQgEMaM5qy6uWk4SfBYfdu9jvBN9nSVDOKRtwb+I3epboOsOX5pZbJNsBJFvmQQ05YMfieIBnYX
	FK2N6dOawd97r/e8RjkTLzmMsiVgrAoEugtviCM3v2WzjgAAAABJRU5ErkJggg==
Copyright: Copyright 2009 James Cloos
OpenPGP: ED7DAEA6; url=http://jhcloos.com/public_key/0xED7DAEA6.asc
OpenPGP-Fingerprint: E9E9 F828 61A4 6EA9 0F2B  63E7 997A 9F17 ED7D AEA6
Date: Fri, 08 Oct 2010 18:02:26 -0400
Message-ID: <m3r5g0filx.fsf@carbon.jhcloos.org>
Lines: 31
MIME-Version: 1.0
Content-Type: text/plain

I have a table which includes a text column containing posix-style
paths.  Ie, matching the regexp "^[^/]+(/[^/]+)*$".

I need to do a query of a number of columns from that table, plus the
count of rows which are "children" of the current row.

The query:

  SELECT count(*) AS nch FROM m WHERE o = (SELECT o FROM m WHERE id=30016)
                AND name ILIKE (SELECT name || '/%' FROM m WHERE id=30016);

selects that extra column given the id.

A view containing all of the columns from m plus a column matching the
above select would cover my needs well.

But I haven't been able to get the syntax right.

The current code (which I did not write) uses one select to get the
values of id it wants, and then iterates through them selecting four
columns from the table and then the above.  As you may imagine, two
selects per row is *slow*.  Maybe 20 rows per second.  I expect a
single, complete select to take < 10 ms.

Again, to be clear, for each row I need the count of other rows which
have the same value for column o and whose name is a child path of the
current row's name.

-JimC
-- 
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6