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.