Tom,
I probably could have written the function like you suggest in this email,
and i probably will look into doing so. This was the first time I tried
creating an index function, and one of the few times i've used plpgsql. In
general i have very little experience with doing this kind of stuff in
postgres (i try to stick to standard SQL as much as possible) and it looks
like i've stumbled onto this problem because of a bad design decision on my
part and a lack of understanding of how index functions work.
Thanks for the suggestion.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Postgres Hackers" <pgsql-hackers@postgresql.org>; "Brian A Hirt"
<bhirt@berkhirt.com>
Sent: Tuesday, December 18, 2001 12:20 PM
Subject: Re: [HACKERS] problems with table corruption continued
> "Brian Hirt" <bhirt@mobygames.com> writes:
> > I was trying to avoid adding additional computed fields to the tables
and
> > maintaining them with triggers, indexing and searching on them. The
index
> > function seemed like an elegant solution to the problem
>
> Understood, but can you write the index function in a way that avoids
> having it do a SELECT to get at data that it hasn't been passed? I'm
> wondering if you can't define the function as just
> f(first_name, last_name) = upper(first_name || ' ' || last_name)
> and create the index on f(first_name, last_name). You haven't shown us
> the queries you expect the index to be helpful for, so maybe this is not
> workable...
>
> regards, tom lane
>