triggers/constraints?
hello,
consider a sample table:
create table item (id serial, parent_id int, path varchar(256));
"id" is a unique identifier of each row,
"parent_id" is an id of another row in the same table or null
what is the right way in postgresql to ensure that the "path" field of
a given row is a prefix of the "path" field of the row referenced by
the "parent_id" field? check constraints? triggers?
please advice, i am new.
thanks in advance
konstantin
I believe you have a design problem. It seems to me that you need two tables;
one with (id, path) and the other with (parent_id, path). Then you can use an
UPDATE trigger on item which checks for a change in path. If it has changed,
then you can update all those records in table "item2" where item2.parent_id
matches item.id with the new prefix.
You generally do not want to update other records in the same table with a
trigger. This will send you into a loop unless you take special measures.
HTH
On Friday 07 October 2005 04:26 pm, ako... saith:
Show quoted text
hello,
consider a sample table:
create table item (id serial, parent_id int, path varchar(256));
"id" is a unique identifier of each row,
"parent_id" is an id of another row in the same table or nullwhat is the right way in postgresql to ensure that the "path" field of
a given row is a prefix of the "path" field of the row referenced by
the "parent_id" field? check constraints? triggers?please advice, i am new.
thanks in advance
konstantin---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly