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

Index on function referring other table

From: "Albert REINER" <areiner(at)tph(dot)tuwien(dot)ac(dot)at>
To: PostgreSQL-Novice <pgsql-novice(at)postgreSQL(dot)org>
Subject: Index on function referring other table
Date: 2001-01-19 22:09:31
Message-ID: 20010119230931.A203@frithjof (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

from the following passage in the man page on create index:

,----[ man l create_index ]
| 	 CREATE [ UNIQUE ] INDEX index_name ON table
| 	     [ USING acc_name ] ( func_name( column [, ... ]) [ ops_name ] )
| ...
| 	 In  the  second syntax shown above, an index is defined on
| 	 the result of a user-specified function func_name  applied
| 	 to  one or more attributes of a single class.  These funcĀ­
| 	 tional indices can be used to obtain fast access  to  data
| 	 based on operators that would normally require some transĀ­
| 	 formation to apply them to the base data.

I get the impression that I can use an index on any function to speed
up access to that particular function, e.g. if I want to match strings
against a certain standardized form. Now consider a function like

| create function whatever(int4) returns text as '
|   declare
|     r record;
|   begin
|     select data into r
| 	from otherTable where = $1
| 	order by whatever limit 1;
|     return;
|   end;' language 'plpgsql';

I might be tempted to have an index on whatever(firstTable.intField)
in order to be able to return this as fast as possible. But as the
function result obviously depends on data in a different table -
otherTable in the above function -, I wonder (a) how the index code
might figure out that a change to otherTable might trigger a change in
the function results and (b) whether maintaining such an index would
not in fact be a very tedious (and, consequently, slow) task.

So I guess it there must be some restriction on the legal functions
for such a construction, but I cannot find anything in the
documentation. Or am I simply wrong?



Albert Reiner                                   <areiner(at)tph(dot)tuwien(dot)ac(dot)at>
Deutsch       *       English       *       Esperanto       *       Latine


pgsql-novice by date

Next:From: Andy HolmanDate: 2001-01-19 22:15:05
Subject: Re: Postgres access using PHP3
Previous:From: John PoltorakDate: 2001-01-19 21:32:48
Subject: Re: Postgres access using PHP3

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