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