From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: tree structure photo gallery date quiery
Date: Tue, 16 Nov 2004 11:29:46 +0000
Message-ID: <200411161129.46131.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: tree structure photo gallery date quiery
In-Reply-To: <200411161129.46131.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox