Content-Type: text/plain;
  charset="us-ascii"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: tree structure photo gallery date quiery
Date: Tue, 16 Nov 2004 11:29:46 +0000
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200411161129.46131.gary.stainburn@ringways.co.uk>

Hi folks.

I'm looking at the possibility of implementing a photo gallery for my 
web site with a tree structure, something like:

create table gallery (
id	serial,
parent int4,
name varchar(40),
primary key (id));

create table photos (
pid serial,
id int4 references gallery not null,
added timestamp,
pfile varchar(128) not null,
pdesc varchar(40) not null,
primary key (pid));

copy "gallery" from stdin;
1	0	Root
2	1	NYMR
3	1	Middleton
4	2	Steam Gala
5	2	Diesel Gala
6	2	From The Footplate
7	3	From The Footplate
\.

copy "photos" from stdin;
1	4	2004-11-10 10:10:00	80135-1.jpg	80135 light-engine
2	4	2004-11-10 12:12:00	6619-1.jpg	6619 on-shed
3	5	2004-10-01 10:00:00	D7628.jpg	Sybilla
4	7	2004-01-01 09:12:12	mm-21.jpg	No. 4 Mathew Murrey
\.

How would I go about creating a view to show a) the number of photos in 
a gallery and b) the timestamp of the most recent addition for a 
gallery, so that it interrogates all sub-galleries?

For example NYMR should return 3, 2004-11-10 12:12, Middleton should 
return 1, 2004-01-01 09:12:12 and Root should return 4, 2004-11-10 
12:12:00
-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000