Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 68FDD476213
	for <pgsql-sql@postgresql.org>; Wed, 11 Dec 2002 20:02:25 -0500 (EST)
Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250])
	by postgresql.org (Postfix) with ESMTP id C85A24760A2
	for <pgsql-sql@postgresql.org>; Wed, 11 Dec 2002 20:02:22 -0500 (EST)
Received: from [216.135.165.74] (account <josh@agliodbs.com>)
	by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2)
	with HTTP id 2277708; Wed, 11 Dec 2002 17:02:40 -0800
From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: trigger to maintain relationships
To: David M <davidgm0@ucia.gov>, pgsql-sql@postgresql.org
X-Mailer: CommuniGate Pro Web Mailer v.4.0.2
Date: Wed, 11 Dec 2002 17:02:40 -0800
Message-ID: <web-2277708@davinci.ethosmedia.com>
In-Reply-To: <3DF7777A.41DDD72B@ucia.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/138
X-Sequence-Number: 10726

David,

> FYI, join should've looked like:
> 
> create function pr_tr_i_nodes() returns opaque
> as '
>     insert into ancestors
>     select NEW.node_id, ancestor_id
>     from NEW left outer join ancestors on (NEW.parent_id =
> ancestors.node_id);
> 
>     return NEW;'
> language 'plpgsql';
> create trigger tr_i_nodes after insert
>     on nodes for each row
>     execute procedure pr_tr_i_nodes();

Ummm ... no.

Within the trigger produre, NEW is a record variable, and its fields
are values.  You cannot SELECT from NEW.  You're also missing the parts
of a PLPGSQL procedure.  What you want is:

create function pr_tr_i_nodes() returns opaque
> as '
DECLARE v_ancestor INT;
BEGIN
SELECT ancestor_id INTO v_ancestor
FROM ancestors WHERE ancestors.node_id = NEW.parent_id;
INSERT INTO ancestors
VALUES ( NEW.node_id, v_ancestor );
>     return NEW;
END;'
> language 'plpgsql';

-Josh Berkus