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:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
> message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
