Skip site navigation (1) Skip section navigation (2)

Re: trigger to maintain relationships

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: David M <davidgm0(at)ucia(dot)gov>, pgsql-sql(at)postgresql(dot)org
Subject: Re: trigger to maintain relationships
Date: 2002-12-12 01:02:40
Message-ID: web-2277708@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: ksqlDate: 2002-12-12 03:36:57
Subject: Backup to data base how ?
Previous:From: Tomasz MyrtaDate: 2002-12-11 23:55:35
Subject: multi-user and multi-level database access

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group