Skip site navigation (1) Skip section navigation (2)

tree structure photo gallery date quiery

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: tree structure photo gallery date quiery
Date: 2004-11-16 11:29:46
Message-ID: 200411161129.46131.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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     


Responses

pgsql-sql by date

Next:From: Achilleus MantziosDate: 2004-11-16 12:30:09
Subject: Re: Move table between schemas
Previous:From: Andrew SullivanDate: 2004-11-16 11:05:38
Subject: Re: Move table between schemas

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group