New pl/pgsql question

Post #69,308 by drewk 12/17/02 12:32:49 PM Reply	New pl/pgsql question Trying to write a recursive function to find the menu level. Table structure is: menu_id; menu_text; menu_parent; Function is: CREATE OR REPLACE FUNCTION "get_menu_level"(menu_tbl) RETURNS integer AS '\nDECLARE\n in_row ALIAS FOR $1;\n level INTEGER;\n parent_level INTEGER;\nBEGIN\n level := 1;\n IF in_row.menu_parent IS NOT NULL THEN\n parent_level := get_menu_level( in_row );\n level := level + parent_level;\n END IF;\n RETURN level;\nEND;\n' LANGUAGE 'plpgsql'; I'm getting: NOTICE: Error occurred while executing PL/pgSQL function get_menu_level\nNOTICE: line 8 at assignment\nERROR: Attribute 'in_row' not found Any ideas? === Microsoft offers them the one thing most business people will pay any price for - the ability to say "we had no choice - everyone's doing it that way." -- [link\|http://z.iwethey.org/forums/render/content/show?contentid=38978\|Andrew Grygus]
Post #69,544 by static 12/18/02 12:41:05 AM Reply	Why not use a set-tree like we have here? Scott has already coded some SQL functions for searching set-trees. Look at the Souce link at the top of any IWETHEY page. In a nutshell, you have put a "left" and a "right" value on each node. The values are assigned by traversing the tree in depth-first order except you assign left to each node on the way down and right on the way up. Start at 1 and increment it after each assignement. So the topmost node gets 1 as left and has the total number of nodes * 2 as its right value. Leaf nodes will have right = left + 1. From there, you can fish out lots of things. All parents of a node are WHERE left < node.left AND right > node.right. Add ORDER BY left DESC LIMIT 1 to get the first. Do a COUNT() instead to get the depth. A node's (right - left - 1)/2 is the number of children. All children can be retrieved with WHERE left > node.left AND right < node.right. I hope I got all that right. Wade. "Ah. One of the difficult* questions."
Post #69,574 by CRConrad 12/18/02 5:29:51 AM Reply	And here, IIRC, is where he got much of it from: Joe Celko's Intelligent Enterprise Magazine [link\|http://www.intelligententerprise.com/cgi-bin/printable.cgi?file=../001020/celko1_1.shtml\|column on Trees in SQL of October 20, 2000] (if you want the pictures, here's the non-print version of that [link\|http://www.intelligententerprise.com/001020/celko1_1.shtml\|October 20, 2000, column on Trees in SQL]). Dunno if Celko changed his mind before that column and after these earlier ones in DBMS Magazine: [link\|http://www.dbmsmag.com/9603d06.html\|A Look at SQL Trees, March 1996], and [link\|http://www.dbmsmag.com/9605d06.html\|Nontraditional Databases, May 1996], or if they're just older versions of the same thing. HTH! [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Actually, I rather like women, and don't particularly care for men, so I don't really mind that women are all completely insane. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=68920\|Andrew Grygus]
Post #69,586 by admin 12/18/02 8:55:33 AM Reply	Yep. From the older articles. I hadn't seen the new one. The older series was more complete. I've done a few things differently to get rid of the self-joins, however. That huge bump in speed we saw a few months ago back was largely due to that optimization. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #69,768 by static 12/18/02 8:18:13 PM Reply	The left-right numbering is the key. Once I know that, I can regenerate a set-tree implementation from nothing. The articles linked to come from an extremely SQL orientation which doesn't suit the way I (currently) think. Makes them a hard read, but I'll perserve because I want to learn how the coversion process works. Fortunately, you spent time a year or so ago beating set-trees it into my head and for that I thank you! It's the kind of elegant design I like implementing. Wade. "Ah. One of the difficult questions."
Post #69,777 by admin 12/18/02 9:07:12 PM Reply	Yep. Celko has some good ideas. I don't know if they are all original, but he's to be credited with popularizing some of them. On the other hand, I got into an email conversation with him once. I was left with the impression that he is quite egotistical, has very few social graces, and as a result is not interested in listening to Ideas Other Than His Own. Reminds me of someone else around here, as a matter of fact. We were discussing combinatorial algorithms in SQL, and I couldn't get across to him that hardcoding column names was a bad idea, and that I needed to be able to cross-combine arbitrary numbers of arbitrary-sized sets. I ended up solving my problem with an arithmetic trick, and I never did get him to understand. :-P Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #69,810 by CRConrad 12/19/02 2:18:40 AM Reply	What, you talkng in the singular? Scott on Joe Celko: I was left with the impression that he is quite egotistical, has very few social graces, and as a result is not interested in listening to Ideas Other Than His Own. Reminds me of someone else around here, as a matter of fact. "Someone"??? There's lots of us like that here! :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Actually, I rather like women, and don't particularly care for men, so I don't really mind that women are all completely insane. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=68920\|Andrew Grygus]
Post #69,821 by static 12/19/02 5:52:21 AM Reply	ObLRPD: "For Wade, it is to laugh. " "Ah. One of the difficult questions."
Post #69,864 by admin 12/19/02 9:21:59 AM Reply	Not on this level... ObLRPD again: Will the real LRPD please stand up? Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #69,893 by CRConrad 12/19/02 10:59:40 AM Reply	Ob(?)LRPD: "Cococabanana Blaps... "... - It's not just for breakfast anymore!" So, since that is one of mine, you ARE ObViously talking about ME, eh?!? :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Actually, I rather like women, and don't particularly care for men, so I don't really mind that women are all completely insane. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=68920\|Andrew Grygus]
Post #69,584 by drewk 12/18/02 8:45:42 AM Reply	Got a solution It turns out I was over-engineering for just a menu system. If I planned to do dynamic heirarchical menus I would use the nested set trees, which I learned about the first time someone posted the articles Christian linked. But I'm just going to do a static menu that is expanded to the appropriate level for the page I'm on. So one query to get the top-level menu, then one query to get the submenu for the section I'm in. I don't think I want to put more than two levels in the main menu, so set trees would be overkill. But, just because I can't leave it alone until I've solved the problem, here's how to do what I originally asked: CREATE OR REPLACE FUNCTION "get_menu_level"(text) RETURNS integer AS '\nDECLARE\n menu_in ALIAS FOR $1;\n parent_id TEXT;\n level INTEGER;\n parent_level INTEGER;\nBEGIN\n level := 1;\n SELECT INTO parent_id menu_parent FROM menu_tbl WHERE menu_id = menu_in;\n IF parent_id IS NOT NULL THEN\n parent_level := get_menu_level(parent_id);\n level := level + parent_level;\n END IF;\n RETURN level;\nEND;\n' LANGUAGE 'plpgsql';\n\nselect *, get_menu_level(menu_id) from menu_tbl; === Microsoft offers them the one thing most business people will pay any price for - the ability to say "we had no choice - everyone's doing it that way." -- [link\|http://z.iwethey.org/forums/render/content/show?contentid=38978\|Andrew Grygus]

Welcome to IWETHEY!