Thread: how do functional indices work?

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?

depesz

--
hubert depesz lubaczewski                          http://www.depesz.pl/
------------------------------------------------------------------------
... vows are spoken to be broken ...                 [enjoy the silence]
... words are meaningless and forgettable ...             [depeche mode]

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

hubert depesz lubaczewski <depesz@depesz.pl> writes:
> 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,

No, it doesn't.  A functional index using a function that depends on any
data other than its explicitly passed parameters is a horribly bad idea.
It WILL fail --- nastily --- as soon as you change the other table.

To help catch this, 7.2 will not allow you to build functional indexes
on functions that are not marked "iscachable".

            regards, tom lane