Thread: Passing arguments to and INDEX function.

I am not sure if the is the best place to ask this question or if it is
better asked on the hackers or sql list, but here it goes...

I have created a new data type (I call it a fieldlist) in postgresql
which is basically a label-value string.  The advantage to this is that
I can effectively extend the columns in my table by just adding a new
label, I don't have to change the actual table structure.  When I do
queries, I do something like:

    SELECT fieldlist_get(fl, 'LAST_NAME') FROM example_table WHERE
fieldlist_get(fl, 'SSN')='123456789';

(the function fieldlist_get looks for the specified label in the field
list and returns the value as a varchar).

Everything works great, but I would also like to be able to use this
function in my definition of an index.  For example, I would like to be
able to do the following:

    CREATE INDEX example_table_ssn_index ON example_table
(fieldlist_get(fl, 'SSN')) ;

But the CREATE INDEX does not (at least according to the documentation
and my trials) support passing arguments to the function.  What I have
done instead is to create a set of new functions where the argument is
implied by the function name (i.e. fieldlist_get_ssn() returns the value
of the SSN field), but I would rather have just one function instead of
many.

So, with that prefix, is there a way to pass an argument to a
CREATE INDEX function?  If not, is this something that would be possible
to add?  If it is possible, is it desireable?  If it is possible and
desireable, where would I start if I were to add it?

Thank you,
Jeff

Jeffery Collins <collins@onyx-technologies.com> writes:
> So, with that prefix, is there a way to pass an argument to a
> CREATE INDEX function?  If not, is this something that would be possible
> to add?  If it is possible, is it desireable?  If it is possible and
> desireable, where would I start if I were to add it?

It'd be a nontrivial bit of work (possibly not the best thing to do as
your first backend project ;-)).  Currently the keys for an index have
a hard-wired representation in pg_index: there's a list of attribute
numbers, which are either N separate keys of a multi-column index or
the N arguments of a function, depending on whether indproc has a value
or not.  There's noplace to put a constant value, unless you can squeeze
it into the int2 slot where the attribute number would go.

Rather than kluging this up still further, the right approach would be
to blow it all away in favor of a list of N arbitrary expressions to be
evaluated to produce the index key values.  The expressions would be
stored as nodetrees, same as we do for column default values (for
example).  Doable, I think, but not too easy.  You'd have to touch a
bunch of code, not only in the backend but in programs like pg_dump.

Whether or not Jeff wants to tackle it, I think the existing TODO item
for this is badly phrased:

* Allow CREATE INDEX zman_index ON test (date_trunc( 'day', zman ) datetime_ops)
  fails index can't store constant parameters

It ought to read

* Allow arbitrary expressions as index keys

            regards, tom lane