Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 077E3475E13
	for <pgsql-sql@postgresql.org>; Tue, 20 Aug 2002 04:07:21 -0400 (EDT)
Received: from mail.gmx.net (mail.gmx.net [213.165.64.20])
	by postgresql.org (Postfix) with SMTP id 80B77475DD5
	for <pgsql-sql@postgresql.org>; Tue, 20 Aug 2002 04:07:19 -0400 (EDT)
Received: (qmail 22343 invoked by uid 0); 20 Aug 2002 08:07:20 -0000
Received: from pd9e7dfdc.dip.t-dialin.net (HELO falks) (217.231.223.220)
	by mail.gmx.net (mp017-rz3) with SMTP; 20 Aug 2002 08:07:20 -0000
From: "Falk Nisius" <falknisius@gmx.de>
To: <pgsql-sql@postgresql.org>
Subject: How to prevent recursion
Date: Tue, 20 Aug 2002 10:07:26 +0200
Message-ID: <NEBBJJEAELOFDDMFKJJFEEFJDIAA.falknisius@gmx.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/288
X-Sequence-Number: 9134


Hi,

I try to save hierarchical Data with an
id that contains strings which represent
the way through the tree. On each depth level
I can have a maxcount number of elements.

If I want move subtrees, I have to change these
id-fields. I want implement this as a RULE in the
Database, that the using application not have to
call any SQL statements in a sequence.

I tried:

CREATE RULE hangup AS
  ON UPDATE TO "products"
  WHERE OLD."id" != NEW."id"
  DO INSTEAD
    UPDATE "products" SET "id"=NEW."id" ||
substring("id",char_length(OLD."id")+1) WHERE "id" LIKE (OLD."id" || '~')

That runs definitely in recursion, and I have no idea to prevent
this. Perhaps it runs with triggers and/or Functions.

Thank for your help
Falk