Обсуждение: creating column content from entry values

I am too new to this environment to know what to call what I want to do.

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.
The function may be a simple as a concatenation, or I may want to do other
things to manipulate the strings that are entered. I think what I want is a
function that I can call when I do the insert like this

insert into mytable (col1, col2, col3) values ('somestring', 'another
string' )

but have the insert fix col3 to be whatever it the function computes it to be.

does this make any sense?


--
David Bear
College of Public Programs/ASU
411 N Central, Phoenix, AZ 85004

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.
Do you want it to be purely read-only and always equal to the function
on the current values of the other columns?  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?  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?

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.

            regards, tom lane

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