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

How can I do this?

From: "Alan Young" <alany(at)qwest(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: How can I do this?
Date: 2000-12-21 20:09:41
Message-ID: 00f801c06b89$f4f28540$d44f58cf@idiglobal.com (view raw or flat)
Thread:
Lists: pgsql-novice
I want to have a table (a category table in this instance) that will do the
following:

reference a field within the same table (you'll see why later)
have a field automatically changed to another row's field value upon
deletion of the referenced field

This is what I've come up with:

create table category (
catid serial primary key,
parentid int references category ( catid ) on delete <?trigger here?>,
catname text unique,
catdesc text
);

I know I need to create a trigger but I'm not sure how to do that.  I've
read the docs, but an example would be great for this particular need.

Also, I know I can do this to update the category with three statements but
I'm pretty sure I can do it in one with a subselect but I'm still fuzzy on
how to do that.  This is a bad mix of pseudo code and sql.

A = category.parentid from row being deleted
B = select parentid from category where catid=A;
update category set parentid=B where parentid=A;
finish deleting row.

Does any of this make sense?

Alan Young
Programmer/Analyst
IDIGlobal.com


In response to

  • Speed at 2000-12-20 16:48:59 from Karla Peralta

Responses

pgsql-novice by date

Next:From: Joel BurtonDate: 2000-12-21 20:17:02
Subject: Re: How can I do this?
Previous:From: Joel BurtonDate: 2000-12-21 19:42:58
Subject: Re: Transactions

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