Re: [SQL] retrieving all rows from a "tree" in one select - how ?
I'll be curious to see the responses to this. I myself deal with this same situation every day. Although we're currently using MySQL but moving it to postgres (which is why I'm on these lists..) > -- select a tree starting with node 1234 and all its descendants: > SELECT * FROM t WHERE id=1234 OR ANCESTOR(t.parent) IS 1234; I've seen some really weird solutions to this. I'm not sure if a subselect can do this or not. I doubt it. Since MySQL limits us greatly we resort to a lookup field for each record in the node. ie. (Forgive ASCII art please) 1 --> 2 --> 4 --> 5 --> 6 --> 8 --> 9 --> 3 --> 7 --> 10 The record for id=9 would have a field index='-1-2-6-8-x' When we want all records under node id=6 we just use: select * from t where index like "%-6-%"; We prefix with '-' for arbitrary level searches. We suffix with -x for an unknown (but good) reason. My memory is leaving me. > -- select the path from tree node 2345 to the root > SELECT * FROM t WHERE id=2345 OR DESCENTANT(t.parent) IS 2345; With our lookup/index field this is trivial. Unfortunately, it makes the application responsible for parsing and is probably not what you're after. Just my two cents. It works very well for us (make the lookup field an index btw) but their is probably a much better way in postgres. I don't remember if postgres allows regexes in the where clause (ie. rlike in mysql) but with that you can "find all nodes 3 or more leaves down from node 123" or even weirder stuff. We have trees with 60,000 nodes 30-40 levels deep. Queries on the tree take very little time at all. Adam Erickson ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Rules and Triggers
Correct me if I'm wrong, but rules constrain the SQL (ie. validation). Triggers are carried out after the SQL is executed and the data is modified. So, to answer your question, I think rules come first. As to which order the rules / triggers are executed probably depends on the order you put them in when you created them but I pulled that straight out of my arse. :) > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Paesold > Sent: Tuesday, September 10, 2002 1:53 PM > To: [EMAIL PROTECTED] > Subject: [SQL] Rules and Triggers > > > Hi all, > > can you tell me in what order rules and triggers are executed? > > First, what comes first, the rules, or the triggers? > And then, in what order are all the rules / triggers executed? > > Regards, > Michael Paesold > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
