tree structure photo gallery date quiery

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: tree structure photo gallery date quiery

On Tuesday 16 November 2004 14:29, Gary Stainburn wrote:
> Hi folks.
>
> I'm looking at the possibility of implementing a photo gallery for my
> web site with a tree structure

> 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?

nested-tree helps you
associate a numeric interval [l,r] with each record of a tree
and let father interval include all its children intervals
and brother intervals never intersect

see the article http://sf.net/projects/redundantdb
for detailed examples and templates



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: tree structure photo gallery date quiery

On Tue, 2004-11-16 at 11:29 +0000, Gary Stainburn wrote:
>
> 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?

There isn't a very simple answer to that question because you don't have
enough information. To make that view, you require there to be a maximum
depth to the galleries (say 3 galleries deep only -- including root) OR
you need another structure which represents the relationship between all
of the galleries.

For the latter, something like gallery_lookup(id, cid, nest_depth):

1 1 0
2 2 0
3 3 0
4 4 0
5 5 0
6 6 0
7 7 0
1 2 1
1 3 1
1 4 2
1 5 2
1 6 2
1 7 2
2 4 1
2 5 1
2 6 1
3 7 1

Now that you know the relationship between them all, you can quickly and
easily determine all galleries underneath the top level one. Sorry,
don't know the technical term, if there is one, for this operation.

Now lets make a pair of views:

CREATE VIEW gallery_aggregate
AS SELECT id, name,
sum(CASE WHEN pid IS NULL THEN 0 ELSE 1 END) AS photocount,
max(added) AS max_added
FROM gallery
LEFT OUTER JOIN photos USING (id)
GROUP BY id, name;

CREATE VIEW gallery_view_you_want
AS SELECT name,
sum(photocount),
max(max_added)
FROM gallery
JOIN gallery_lookup AS gl USING (id)
JOIN gallery_aggregate AS ga ON (gl.cid = ga.id)
GROUP BY name;


There are plenty of steps you can take to make this both faster and/or
use less storage; optimize aggregates, use a function to calculate the
'gallery_lookup' contents, etc.

None of this has been tested.

--


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: tree structure photo gallery date quiery

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

You don't really want a tree structure, because one day you'll want to
put the same photo in two galleries. Suppose you take a very interesting
photo of celery during your trip to china, you might want to create a
'Trip to China' folder, and also a 'Celery' folder for your other celery
photos... well, if you don't like vegetables, it also works with people,
moods, geographic regions, themes, etc. You could define this structure :

You could then define tables describing themes, and/or keywords, link
photos with these themes and keywords, and define a folder as either being
a specific collection of photos, or as a collection of one or several
themes.

From a tree, it becomes a bit more like a graph.

Themes can also be organized and relationed together.

This opens the path to easy searching and cataloguing ; is not that much
more difficult to do, and in the end you'll have a much better system.

> 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?

If you're concerned about performance, you should do this in a
materialized view updated with triggers. If you can afford a seq scan on
every time, a few stored procs should do the trick.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: tree structure photo gallery date quiery

On Tuesday 16 November 2004 1:08 pm, sad wrote:
> On Tuesday 16 November 2004 14:29, Gary Stainburn wrote:
> > Hi folks.
> >
> > I'm looking at the possibility of implementing a photo gallery for
> > my web site with a tree structure
> >
> > 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?
>
> nested-tree helps you
> associate a numeric interval [l,r] with each record of a tree
> and let father interval include all its children intervals
> and brother intervals never intersect
>
> see the article http://sf.net/projects/redundantdb
> for detailed examples and templates

Hi Sad,

I had actually started working on this because I found an old list
posting archived on the net at
http://www.net-one.de/~ks/WOoK/recursive-select.

As you can see below, I've got the tree structure working and can select
both a node's superiors and it's subordinates. Using these I can also
find a node's last added date and photo count.

However, I've got two problems. Firstly, below I've got the two example
selects for listing owners and owned nodes. I can't work out how to
convert these two parameterised selects into views.

Secondly, in order to get the results shown here, I've had to write
two seperate but similar pl/pgsql functions to return the photo_count
and photo_updated columns, which result in
2 * select per call * twice per line * 7 lines = 28 selects

Is there a more efficient way?

nymr=# select *, photo_count(id), photo_updated(id) from gallery;
id | parent | name | photo_count | photo_updated
----+--------+--------------------+-------------+----------- -------------
1 | 0 | Root | 4 | 2004-11-10 12:12:00+00
2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00
3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00
4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00
5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01
6 | 2 | From The Footplate | 0 |
7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00
(7 rows)

Below is everything I have so far, including one of the functions I'm
using:

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));


create table tree ( -- seperate for now to ease development
id int4 references gallery not null,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt) );


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
\.

copy "tree" from stdin;
1 1 14
2 2 9
3 10 13
4 3 4
5 5 6
6 7 8
7 11 12
\.

-- select leaf and parents
-- want to convert to a view so I can type something like
-- 'select * from root_path where id = 7;
nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2
where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7;
id | parent | name
----+--------+--------------------
1 | 0 | Root
3 | 1 | Middleton
7 | 3 | From The Footplate
(3 rows)

-- Select parent and subordinates - also want to convert to view
nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where
g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1;
id | lft | rgt | id | parent | name
----+-----+-----+----+--------+--------------------
1 | 1 | 14 | 1 | 0 | Root
2 | 2 | 9 | 2 | 1 | NYMR
3 | 10 | 13 | 3 | 1 | Middleton
4 | 3 | 4 | 4 | 2 | Steam Gala
5 | 5 | 6 | 5 | 2 | Diesel Gala
6 | 7 | 8 | 6 | 2 | From The Footplate
7 | 11 | 12 | 7 | 3 | From The Footplate
(7 rows)

-- use the one above to select photos - another view
nymr=# select count(pid), max(added) from photos where id in (
nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
p2.lft and p2.rgt and p2.id = 1
nymr(# );
count | max
-------+------------------------
4 | 2004-11-10 12:12:00+00
(1 row)

nymr=# select count(pid), max(added) from photos where id in (
nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
p2.lft and p2.rgt and p2.id = 2
nymr(# );
count | max
-------+------------------------
3 | 2004-11-10 12:12:00+00
(1 row)

nymr=# select count(pid), max(added) from photos where id in (
nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
p2.lft and p2.rgt and p2.id = 3
nymr(# );
count | max
-------+------------------------
1 | 2004-01-01 09:12:12+00
(1 row)

Here is the photo_count function, photo_updates just has differnt
attribute names/types

create function photo_count(int4) returns int4 as 'DECLARE
gallery_id alias for $1;
pcount int4;
begin
select count(pid) into pcount from photos where id in (
select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft
and p2.rgt and p2.id = gallery_id
);
return pcount;
end' language 'plpgsql';

--
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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: tree structure photo gallery date quiery

Gary,

If you're not to worried about tying yourself to Postgres and you're
sure you want to create a tree structure, you may want to check out
the ltree contrib module. It will allow you to create an index over
the entire tree, and will allow you to use real names instead of INTs
for the nodes in the tree. ltree will also allow you to have one
particular node at different points in the tree.

On Wed, 17 Nov 2004 00:35:50 +0000, Gary Stainburn
wrote:
> On Tuesday 16 November 2004 1:08 pm, sad wrote:
>
>
> > On Tuesday 16 November 2004 14:29, Gary Stainburn wrote:
> > > Hi folks.
> > >
> > > I'm looking at the possibility of implementing a photo gallery for
> > > my web site with a tree structure
> > >
> > > 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?
> >
> > nested-tree helps you
> > associate a numeric interval [l,r] with each record of a tree
> > and let father interval include all its children intervals
> > and brother intervals never intersect
> >
> > see the article http://sf.net/projects/redundantdb
> > for detailed examples and templates
>
> Hi Sad,
>
> I had actually started working on this because I found an old list
> posting archived on the net at
> http://www.net-one.de/~ks/WOoK/recursive-select.
>
> As you can see below, I've got the tree structure working and can select
> both a node's superiors and it's subordinates. Using these I can also
> find a node's last added date and photo count.
>
> However, I've got two problems. Firstly, below I've got the two example
> selects for listing owners and owned nodes. I can't work out how to
> convert these two parameterised selects into views.
>
> Secondly, in order to get the results shown here, I've had to write
> two seperate but similar pl/pgsql functions to return the photo_count
> and photo_updated columns, which result in
> 2 * select per call * twice per line * 7 lines = 28 selects
>
> Is there a more efficient way?
>
> nymr=# select *, photo_count(id), photo_updated(id) from gallery;
> id | parent | name | photo_count | photo_updated
> ----+--------+--------------------+-------------+----------- -------------
> 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00
> 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00
> 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00
> 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00
> 5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01
> 6 | 2 | From The Footplate | 0 |
> 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00
> (7 rows)
>
> Below is everything I have so far, including one of the functions I'm
> using:
>
>
>
> 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));
>
>
> create table tree ( -- seperate for now to ease development
> id int4 references gallery not null,
> lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
> CONSTRAINT order_okay CHECK (lft < rgt) );
>
>
>
>
> 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
> \.
>
> copy "tree" from stdin;
> 1 1 14
> 2 2 9
> 3 10 13
> 4 3 4
> 5 5 6
> 6 7 8
> 7 11 12
> \.
>
> -- select leaf and parents
> -- want to convert to a view so I can type something like
> -- 'select * from root_path where id = 7;
> nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2
> where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7;
> id | parent | name
> ----+--------+--------------------
> 1 | 0 | Root
> 3 | 1 | Middleton
> 7 | 3 | From The Footplate
> (3 rows)
>
> -- Select parent and subordinates - also want to convert to view
> nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where
> g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1;
> id | lft | rgt | id | parent | name
> ----+-----+-----+----+--------+--------------------
> 1 | 1 | 14 | 1 | 0 | Root
> 2 | 2 | 9 | 2 | 1 | NYMR
> 3 | 10 | 13 | 3 | 1 | Middleton
> 4 | 3 | 4 | 4 | 2 | Steam Gala
> 5 | 5 | 6 | 5 | 2 | Diesel Gala
> 6 | 7 | 8 | 6 | 2 | From The Footplate
> 7 | 11 | 12 | 7 | 3 | From The Footplate
> (7 rows)
>
> -- use the one above to select photos - another view
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 1
> nymr(# );
> count | max
> -------+------------------------
> 4 | 2004-11-10 12:12:00+00
> (1 row)
>
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 2
> nymr(# );
> count | max
> -------+------------------------
> 3 | 2004-11-10 12:12:00+00
> (1 row)
>
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 3
> nymr(# );
> count | max
> -------+------------------------
> 1 | 2004-01-01 09:12:12+00
> (1 row)
>
> Here is the photo_count function, photo_updates just has differnt
> attribute names/types
>
> create function photo_count(int4) returns int4 as 'DECLARE
> gallery_id alias for $1;
> pcount int4;
> begin
> select count(pid) into pcount from photos where id in (
> select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft
> and p2.rgt and p2.id = gallery_id
> );
> return pcount;
> end' language 'plpgsql';
>
>
>
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: tree structure photo gallery date quiery

Gary,

if you need really fast solution for you task and dont't afraid
non-standard soltion, take a look on contrib/ltree module.
http://www.sai.msu.su/~megera/postgres/gist/ltree/

Oleg
On Wed, 17 Nov 2004, Gary Stainburn wrote:

> On Tuesday 16 November 2004 1:08 pm, sad wrote:
>> On Tuesday 16 November 2004 14:29, Gary Stainburn wrote:
>>> Hi folks.
>>>
>>> I'm looking at the possibility of implementing a photo gallery for
>>> my web site with a tree structure
>>>
>>> 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?
>>
>> nested-tree helps you
>> associate a numeric interval [l,r] with each record of a tree
>> and let father interval include all its children intervals
>> and brother intervals never intersect
>>
>> see the article http://sf.net/projects/redundantdb
>> for detailed examples and templates
>
> Hi Sad,
>
> I had actually started working on this because I found an old list
> posting archived on the net at
> http://www.net-one.de/~ks/WOoK/recursive-select.
>
> As you can see below, I've got the tree structure working and can select
> both a node's superiors and it's subordinates. Using these I can also
> find a node's last added date and photo count.
>
> However, I've got two problems. Firstly, below I've got the two example
> selects for listing owners and owned nodes. I can't work out how to
> convert these two parameterised selects into views.
>
> Secondly, in order to get the results shown here, I've had to write
> two seperate but similar pl/pgsql functions to return the photo_count
> and photo_updated columns, which result in
> 2 * select per call * twice per line * 7 lines = 28 selects
>
> Is there a more efficient way?
>
> nymr=# select *, photo_count(id), photo_updated(id) from gallery;
> id | parent | name | photo_count | photo_updated
> ----+--------+--------------------+-------------+----------- -------------
> 1 | 0 | Root | 4 | 2004-11-10 12:12:00+00
> 2 | 1 | NYMR | 3 | 2004-11-10 12:12:00+00
> 3 | 1 | Middleton | 1 | 2004-01-01 09:12:12+00
> 4 | 2 | Steam Gala | 2 | 2004-11-10 12:12:00+00
> 5 | 2 | Diesel Gala | 1 | 2004-10-01 10:00:00+01
> 6 | 2 | From The Footplate | 0 |
> 7 | 3 | From The Footplate | 1 | 2004-01-01 09:12:12+00
> (7 rows)
>
> Below is everything I have so far, including one of the functions I'm
> using:
>
> 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));
>
>
> create table tree ( -- seperate for now to ease development
> id int4 references gallery not null,
> lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
> CONSTRAINT order_okay CHECK (lft < rgt) );
>
>
> 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
> \.
>
> copy "tree" from stdin;
> 1 1 14
> 2 2 9
> 3 10 13
> 4 3 4
> 5 5 6
> 6 7 8
> 7 11 12
> \.
>
> -- select leaf and parents
> -- want to convert to a view so I can type something like
> -- 'select * from root_path where id = 7;
> nymr=# select p2.id, g.parent, g.name from gallery g, tree p1, tree p2
> where g.id = p2.id and p1.lft between p2.lft and p2.rgt and p1.id = 7;
> id | parent | name
> ----+--------+--------------------
> 1 | 0 | Root
> 3 | 1 | Middleton
> 7 | 3 | From The Footplate
> (3 rows)
>
> -- Select parent and subordinates - also want to convert to view
> nymr=# select p1.*, g.* from tree as p1, tree as p2, gallery g where
> g.id = p1.id and p1.lft between p2.lft and p2.rgt and p2.id = 1;
> id | lft | rgt | id | parent | name
> ----+-----+-----+----+--------+--------------------
> 1 | 1 | 14 | 1 | 0 | Root
> 2 | 2 | 9 | 2 | 1 | NYMR
> 3 | 10 | 13 | 3 | 1 | Middleton
> 4 | 3 | 4 | 4 | 2 | Steam Gala
> 5 | 5 | 6 | 5 | 2 | Diesel Gala
> 6 | 7 | 8 | 6 | 2 | From The Footplate
> 7 | 11 | 12 | 7 | 3 | From The Footplate
> (7 rows)
>
> -- use the one above to select photos - another view
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 1
> nymr(# );
> count | max
> -------+------------------------
> 4 | 2004-11-10 12:12:00+00
> (1 row)
>
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 2
> nymr(# );
> count | max
> -------+------------------------
> 3 | 2004-11-10 12:12:00+00
> (1 row)
>
> nymr=# select count(pid), max(added) from photos where id in (
> nymr(# select p1.id from tree as p1, tree as p2 where p1.lft between
> p2.lft and p2.rgt and p2.id = 3
> nymr(# );
> count | max
> -------+------------------------
> 1 | 2004-01-01 09:12:12+00
> (1 row)
>
> Here is the photo_count function, photo_updates just has differnt
> attribute names/types
>
> create function photo_count(int4) returns int4 as 'DECLARE
> gallery_id alias for $1;
> pcount int4;
> begin
> select count(pid) into pcount from photos where id in (
> select p1.id from tree as p1, tree as p2 where p1.lft between p2.lft
> and p2.rgt and p2.id = gallery_id
> );
> return pcount;
> end' language 'plpgsql';
>
>

Regards,
Oleg
____________________________________________________________ _
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: tree structure photo gallery date quiery

On Wed, 17 Nov 2004, Achilleus Mantzios wrote:

> O Oleg Bartunov ?????? ???? Nov 17, 2004 :
>
>> Gary,
>>
>> if you need really fast solution for you task and dont't afraid
>> non-standard soltion, take a look on contrib/ltree module.
>> http://www.sai.msu.su/~megera/postgres/gist/ltree/
>>
>> Oleg
>
> Oleg how would you compare an ltree solution against a
> genealogical approach using intarray??
> i.e. store for each node, its path to root,
> e.g. path='{4,2,7}'::int4[] where 4 is the id of the father,
> 2 of the grandfather, and 7 of the root, whereas root has
> path is null.
> That way we can do really fast queries using ~, have indexes
> on level (path length),first (immediate ancestor),last (root)
> using C functions that we can easily write and so forth.
> I have extensively used this approach with success.
> Can you comment on the pros and cons of each? (int[] with intarray vs
> ltree).

well, internally both approaches are very much the same :)
if you don't need ltree features like human readable path

But I'd like to have some real numbers. Could you spent some time and
create test suite and compare genealogical approach and ltree ?
I'll add resuts to docs.

>
> Thanx
>> ____________________________________________________________ _
>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>> Sternberg Astronomical Institute, Moscow University (Russia)
>> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>> phone: +007(095)939-16-83, +007(095)939-23-83
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if your
>> joining column's datatypes do not match
>>
>
>

Regards,
Oleg
____________________________________________________________ _
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: tree structure photo gallery date quiery

O Oleg Bartunov Ýãñáøå óôéò Nov 17, 2004 :

> Gary,
>
> if you need really fast solution for you task and dont't afraid
> non-standard soltion, take a look on contrib/ltree module.
> http://www.sai.msu.su/~megera/postgres/gist/ltree/
>
> Oleg

Oleg how would you compare an ltree solution against a
genealogical approach using intarray??
i.e. store for each node, its path to root,
e.g. path='{4,2,7}'::int4[] where 4 is the id of the father,
2 of the grandfather, and 7 of the root, whereas root has
path is null.
That way we can do really fast queries using ~, have indexes
on level (path length),first (immediate ancestor),last (root)
using C functions that we can easily write and so forth.
I have extensively used this approach with success.
Can you comment on the pros and cons of each? (int[] with intarray vs
ltree).

Thanx
> ____________________________________________________________ _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster