Re: how do functional indices work?

hubert depesz lubaczewski wrote:
>
> hi
> i have a question.
> let's assume i have table users which is (id int4, person_id int4) - pkey'ed
> on id with index on person_id.
> next i have table people (id int4, fullname text) with pkey on id.
> there is a foreign key between the two tables on users.person_id => people.id.
> now i wrote a function, which given user id returns it's person's name. quite
> simple function.
> not i want to make a index:
> create index test on users (myMagicalFunction(id));
> this of course works, but the question is:
> how this index will work if i'll modify the fullname in people table? would it
> be automatically updated? if yes then how pgsql knows where to update this
> index? if no - is there any possible workaround that can be done?
>

I believe what you are looking for is some triggers for your foreign
keys..

Perhaps you should re-visit the docs and try to find the CREATE TABLE
section =]

In it you'll find

REFERENCES Constraint

[ CONSTRAINT name ] REFERENCES reftable [ ( refcolumn ) ]
[ MATCH matchtype ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY checktime ]

=]

So perhaps you could try:

CREATE TABLE people
(
id INT4 PRIMARY KEY,
fullname TEXT
);

CREATE TABLE USERS
(
id INT4,
person_id INT4 REFERENCES people(id) ON DELETE CASCADE ON UPDATE
CASCADE
);

I think =[ I hope that works =]

Cheers,

John Clark
--
/) John Clark Naldoza y Lopez (\
/ ) Software Design Engineer III ( \
_( (_ _ Web-Application Development _) )_
(((\ \> /_> Cable Modem Network Management System <_\ </ /)))
(\\\\ \_/ / NEC Telecom Software Phils., Inc. \ \_/ ////)
\ / \ /
\ _/ phone: (+63 32) 233-9142 loc. 3113 \_ /
/ / cellphone: (+63 919) 399-4742 \ \
/ / email: njclark(at)ntsp(dot)nec(dot)co(dot)jp \ \

"Intelligence is the ability to avoid doing work, yet getting the work
done"
--Linus Torvalds