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@ntsp.nec.co.jp                        \   \


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