IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Need examples for pl/pgsql
The examples in the Postgres docs aren't very comprehensive. Here's what I'm trying to do. I have a table that holds text and links for a menu system. Submenu items have a field that points back to the parent item id. Fields are:

item_id;
item_text;
item_url;
item_parent;

If item_parent is not set, it is a top-level item. If it is set, this item is on a sub-menu of the parent item.

I tried setting a foreign_key constraint on item_parent referencing item_id but it wouldn't work. Then I tried a pl/pgsql function to update item_parent with a trigger on item_id. Couldn't figure out how to pass the old and new values to/from the trigger.

Any tips on the easiest way to do this? Or a good source of examples would be nice, too.
===
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]
New Try this...
I tried setting a foreign_key constraint on item_parent referencing item_id but it wouldn't work. Then I tried a pl/pgsql function to update item_parent with a trigger on item_id. Couldn't figure out how to pass the old and new values to/from the trigger.
CREATE SEQUENCE Person_seq START 2;\n\nCREATE TABLE Person (\n  P_ID            integer      DEFAULT nextval('Person_seq') PRIMARY KEY,\n  P_FName         varchar(20)                                NOT NULL,\n  P_MName         varchar(1)                                     NULL,\n  P_LName         varchar(20)                                NOT NULL,\n[Some fields edited for brevity]\n  P_CreatedBy     integer      REFERENCES Person(P_ID)       NOT NULL,\n  P_EditedDate    datetime                                   NOT NULL,\n  P_EditedBy      integer      REFERENCES Person(P_ID)       NOT NULL\n);
That's the SQL I'm using to create a Person table in a PostgreSQL 7.2 database. It's got a foreign key reference to an item in the same table, which appears to be what you're after.

If that's not what you're looking for, I might need a bit of a more detailed explanation...

HTH.
-YendorMike

[link|http://www.hope-ride.org/|http://www.hope-ride.org/]
New Trying it now
It's got a foreign key reference to an item in the same table, which appears to be what you're after.

That's exactly what I'm looking for. Will try it now.
===
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]
New Here's the problem
CREATE TABLE "menu_tbl" (\n   "menu_id" varchar(20) NOT NULL,\n   "menu_text" varchar(25) NOT NULL,\n   "menu_url" varchar(50) NOT NULL,\n   "menu_parent" varchar(20) REFERENCES menu_tbl(menu_id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,\n   CONSTRAINT "menu_tbl_pkey" PRIMARY KEY ("menu_id")\n);\n

Problem is, when I try to update "menu_id" when there is a matching "menu_parent" I get
ERROR:  referential integrity violation - key referenced from menu_tbl not found in menu_tbl

So I can get the foreign key working, but I can't get it to cascade the updates.
===
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]
New And the solution
I need to explicitly begin a transaction before issuing the update command and commit after.
===
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]
     Need examples for pl/pgsql - (drewk) - (4)
         Try this... - (Yendor) - (3)
             Trying it now - (drewk) - (2)
                 Here's the problem - (drewk) - (1)
                     And the solution - (drewk)

Hey, you sass that hoopy Ford Prefect?
76 ms