[SQL] Move table between schemas
Hello, Is there an easy way to move a table to another schema in PostgreSQL 7.4? ALTER TABLE and ALTER SCHEMA don't have this options. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Move table between schemas
On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote: > Hello, > > Is there an easy way to move a table to another schema in PostgreSQL 7.4? > > ALTER TABLE and ALTER SCHEMA don't have this options. CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable oughta work. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] 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: [SQL] Move table between schemas
O Andrew Sullivan έγραψε στις Nov 16, 2004 : > On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote: > > Hello, > > > > Is there an easy way to move a table to another schema in PostgreSQL 7.4? > > > > ALTER TABLE and ALTER SCHEMA don't have this options. > > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable > > oughta work. What about indexes, constraints, sequences,etc...??? > > A > > -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] tree structure photo gallery date quiery
On Tue, 2004-11-16 at 11:29 +, 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
[SQL] Table definition
Title: Les consultants Interaction | stationery ï Hi, Anybody know how to obtain the table definition in text. Something like "select definition from pg_catalog.pg_views where viewname = 'xxx'" but for a table. Thanks Bruno
Re: [SQL] Table definition
Am Dienstag, 16. November 2004 15:04 schrieb Bruno Prévost: > Anybody know how to obtain the table definition in text. Use pg_dump. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table definition
Title: Les consultants Interaction | stationery Probably there is no direct way get the definition (i could not find one atleast). You can probably write a custom function which gives all columns, indexes and formulate a create table statement (text definition). Let us know if you find a way out. - Goutam From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruno PrévostSent: Tuesday, November 16, 2004 9:04 AMTo: [EMAIL PROTECTED]Subject: [SQL] Table definition Hi, Anybody know how to obtain the table definition in text. Something like "select definition from pg_catalog.pg_views where viewname = 'xxx'" but for a table. Thanks Bruno Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited.
Re: [SQL] Table definition
Bruno Prévost wrote: >Anybody know how to obtain the table definition in text. Not quite sure if this is quite what you're after, but would: $ pg_dump -st foo help at all? It gives out the SQL that you would need to enter to re-create the table. Sam ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table definition
I need to use it in sql. Thk Bruno - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Bruno Prévost" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, November 16, 2004 10:16 AM Subject: Re: [SQL] Table definition Am Dienstag, 16. November 2004 15:04 schrieb Bruno Prévost: Anybody know how to obtain the table definition in text. Use pg_dump. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Table definition
Am Dienstag, 16. November 2004 16:40 schrieb Bruno Prévost: > I need to use it in sql. There is no direct way to do this in SQL, but I can offer you the following alternative: CREATE FUNCTION get_table_definition(text) RETURNS text AS ' #!/bin/sh pg_dump -t $1 ' LANGUAGE plsh; :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Table definition
It could help me, but pg_dump give all foreigns key and index creation. I'm not sure if it's not better to write a custom function in plpgsql. Bruno - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Bruno Prévost" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, November 16, 2004 11:13 AM Subject: Re: [SQL] Table definition Am Dienstag, 16. November 2004 16:40 schrieb Bruno Prévost: I need to use it in sql. There is no direct way to do this in SQL, but I can offer you the following alternative: CREATE FUNCTION get_table_definition(text) RETURNS text AS ' #!/bin/sh pg_dump -t $1 ' LANGUAGE plsh; :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] select using regexp does not use indexscan
[EMAIL PROTECTED] (Tom Lane) wrote in message news:<[EMAIL PROTECTED]>... > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 9 Nov 2004, carex wrote: > >> And it works also perfectly with Gentoo. > >> So,is this a typical "Redhat Enterprise" problem ? > >> Or do I overlook something ?? > > > IIRC, in 7.3.x, index scans are only considered in "C" locale for > > regexp/LIKE. In 7.4.x, non-"C" locale databases can use a special > > index of a different opclass (_pattern_ops I believe). > > Not sure if this answer was explicit enough, so: evidently the database > was initdb'd in "C" locale on Gentoo, but in some other locale on Red Hat. > The only "typical Red Hat problem" is that they are more enthusiastic > about setting up non-C default locales than some other distros. > > regards, tom lane > > ---(end of broadcast)--- Thank you so much. It is indeed clearer now. So I did an initdb --locale=C -D /path/to/data rebuild my database and started my "select" again. I could see my index was used even when host~'^tna2'; Thanks again. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] View and subselect related questions
I have two data tables AUTHORS and BOOKS, and one indirection table
AUTHOR_BOOKS which allow me to make n:m links. Now I want to create view
that allow user to select all books of specyfic author - user should use
query like this SELECT * FROM booksvw WHERE idauthor=xxx.
I know two queries that could solve my problem
1)SELECT * FROM books INNER JOIN authors_books ON
book.id=authors_books.idbook AND authors_books.idauthor=:xxx
Alternative I can use query
2)SELECT books.* FROM books WHERE id IN (SELECT authors_books.idbooks
FROM authors_books WHERE authors_books.idauthor=:xxx)
I think that second query is faster in my case (most of books have only
one author),
Logs :
1)
Merge Join (cost=17.13..756.15 rows=5 width=116)
Merge Cond: ("outer".id = "inner".idbook)
-> Index Scan using book_pkey on books (cost=0.00..709.89 rows=11626
width=116)
-> Sort (cost=17.13..17.14 rows=5 width=4)
Sort Key: autor_books.idbooks
-> Index Scan using autor_idx on autor_books (cost=0.00..17.07
rows=5 width=4)
Index Cond: (idautor = 453)
2)Nested Loop (cost=17.08..415.67 rows=1 width=116)
Join Filter: ("inner".id = "outer".idbook)
-> HashAggregate (cost=17.08..17.08 rows=1 width=4)
-> Index Scan using author_idx on autors_books
(cost=0.00..17.07 rows=5 width=4)
Index Cond: (idauthor = 453)
-> Seq Scan on books (cost=0.00..253.26 rows=11626 width=116)
The first case I can easily transform to view. Second is harder. I don't
know if there are a possibility to transport clauses from view WHERE
part to sub selects. Is this possible?
James Kan
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Counting Distinct Records
I am wondering whether the following two forms of SELECT statements are logically equivalent: SELECT COUNT( DISTINCT table.column ) ... and SELECT DISTINCT COUNT( * ) ... If they are the same, then why is the latter query much slower in postgres when applied to the same FROM and WHERE clauses? Furthermore, is there a better way of performing this sort of operation in postgres (or just in SQL in general)? Thanks! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Counting Distinct Records
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: > I am wondering whether the following two forms of SELECT statements are > logically equivalent: > > SELECT COUNT( DISTINCT table.column ) ... > > and > > SELECT DISTINCT COUNT( * ) ... Not in general. The former counts how many distinct table.column values there are. The distinct in the latter would be basically meaningless unless there's a group by involved. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Counting Distinct Records
Is there another way to accomplish what the former is doing, then? For practical reasons, I'd like to come up with something better. For theoretical curiosity, I'd like to know whether there's a way to combine COUNT and DISTINCT that still allows one to reference * rather than naming specific columns without grouping. If I resort to GROUP BY, is there an efficient way of counting all the groups, or would it just be something like: SELECT COUNT ( * ) FROM ( SELECT ... GROUP BY ... ); -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 16, 2004, at 2:03 PM, Stephan Szabo wrote: On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: I am wondering whether the following two forms of SELECT statements are logically equivalent: SELECT COUNT( DISTINCT table.column ) ... and SELECT DISTINCT COUNT( * ) ... Not in general. The former counts how many distinct table.column values there are. The distinct in the latter would be basically meaningless unless there's a group by involved. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] finding gaps in dates
I have a logging application that should produce an entry in the
database every minute or so, give or take a few seconds.
I'm interested in finding out
a: what minutes don't have a record and
b: periods where the gap exceeded a certain amount of time.
The only way I can think of to do it is to create a set returning
function that accepts a begin and end date and returns a
date_trunc('minute', ...) for each minute between then and then doing
Select ts from srf(now() - '30 days'::inteval, now()) where ts not in
(select distinct date_trunc('minute', ts) from table where ts between
now() - '30 days'::inteval and now());
Of course, that seems extremely tedious to me (not tedious to code,
but tedious for the db to process).
If anyone can suggest a better way I'd really appreciate it.
--
Matthew Nuzum | Makers of "Elite Content Management System"
www.followers.net | View samples of Elite CMS in action
[EMAIL PROTECTED] | http://www.followers.net/portfolio/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] finding gaps in dates
I have a logging application that should produce an entry in the database every minute or so, give or take a few seconds. I'm interested in finding out a: what minutes don't have a record and b: periods where the gap exceeded a certain amount of time. Is this not the same question ? Answer to a: If your script is set to run at every minute + 00 seconds, if it ever runs one second earlier, timestamp-truncate will keep the previous minute and you're screwed. A simple solution would be to have your script run every minute + 30 seconds. Answer to b: If you can do the following : examine the records in chronological order, every time computing the delay between record N and record N-1 ; if this delay is not one minute +/- a few seconds, you have detected an anomaly. Problem : you need to scan the whole table for anomalies every time. Solution : put an ON INSERT trigger on your log table which : - checks the current time for sanity (ie. is it +/- a few seconds from the expected time ?) This solves part of a) - looks at the timestamp of the latest row, computes the difference with the inserted one, and if > than 1 minute + a few seconds, inserts a row in an anomaly logging table. This solves the rest of a) and b) It's just an additional SELECT x FROM table ORDER BY timestamp DESC LIMIT 1 which has a negligible performance impact compared to your insert. ---(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: [SQL] Move table between schemas
On Tue, Nov 16, 2004 at 02:30:09PM +0200, Achilleus Mantzios wrote: > > > > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable > > > > oughta work. > > What about indexes, constraints, sequences,etc...??? You'll have to create those too, I'm afraid. I don't know of a way to move tables from one schema to another otherwise. You could do all the dependencies with a pg_dump -t, I suspect. Not tested that, though. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Counting Distinct Records
On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: > Is there another way to accomplish what the former is doing, then? The only thing I can think of is a subselect in from that uses distinct. select count(*) from (select distinct ...) foo That also theoretically allows you to use select distinct * inside the subselect. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Counting Distinct Records
Hmm. I was more interested in using COUNT( * ) than DISTINCT *. I want a count of all rows, but I want to be able to specify which columns are distinct. That's definitely an interesting approach, but testing doesn't show it to be appreciably faster. If I do a DISTINCT *, postgres will attempt to guarantee that there are no duplicate values across all columns rather than a subset of columns? Is that right? Anyway, I was just wondering if there were any best practices out there for counting distinct values in sets of values that might not themselves be distinct. Thanks for the tips so far! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 16, 2004, at 4:34 PM, Stephan Szabo wrote: On Tue, 16 Nov 2004, Thomas F.O'Connell wrote: Is there another way to accomplish what the former is doing, then? The only thing I can think of is a subselect in from that uses distinct. select count(*) from (select distinct ...) foo That also theoretically allows you to use select distinct * inside the subselect. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] 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, namevarchar(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,
Re: [SQL] 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 +, Gary Stainburn <[EMAIL PROTECTED]> 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, > namevarchar(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 |
