On Thursday 21 February 2008 19:17, Tom Lane wrote:
> David Bear <david.bear@asu.edu> writes:
> > Breifly, I want to do an insert where the values for three of the
> > attributes are combined via a function and make up the value for the
> > fourth attribute.
>
> Well, there's more than one way to do it. What you didn't tell us is
> just how tightly you want to bind column 4 to be func-of-cols-1-2-3.
Thanks for your kind response. I guess I didn't give enough information to
have anyone give me a complete solution. Your questions below help me a
little more and I will attempt to outline my use case.
> Do you want it to be purely read-only and always equal to the function
> on the current values of the other columns?
Yes, I want it to be purely read only. The purpose of this is to generate
a 'canonical string' based upon the input of some values. However, I do not
yet know what the cononical form of the string would be. So I wanted to
abstract it into a function.
> Do you want to force it
> to be that way on initial insertion of the row, but subsequent updates
> could allow the columns to diverge?
I do not ever want it to change. There will never be updates to the other
columns in the tuple.
> Do you merely want it as a default
> that could be overridden during the insert? Also, is the function
> expensive enough that you really want to precompute it at insert time
> and store the output; or maybe it should be just a "virtual" column
> where the function is computed on demand during readout?
I don' t thing the function will be expensive. It will be mostly string
slicing, casing, etc. I would rather have the function that does it
associated with the data base rather than code that generates and insert
statement.
>
> Depending on what you think about these questions, you might choose to
> not store column 4 physically at all, but just have it be part of a view
> wherein the function is computed on-the-fly. Or you could use an ON
> INSERT and/or ON UPDATE trigger, perhaps with different degrees of
> aggressiveness about whether it overrides a pre-supplied value for
> column 4.
Okay, no I think what I am asking for as an insert trigger. The value must be
stored as the table is a lookup table to control entry in other tables. Thus,
the field that is generated will be a foreign key for other attributes in
other relations.
>
> regards, tom lane
--
David Bear
College of Public Programs/ASU
411 N Central, Phoenix, AZ 85004