Re: creating column content from entry values

From: David Bear <david(dot)bear(at)asu(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: creating column content from entry values
Date: 2008-02-22 22:51:57
Message-ID: 200802221551.57262.david.bear@asu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

On Thursday 21 February 2008 19:17, Tom Lane wrote:
> David Bear <david(dot)bear(at)asu(dot)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

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Asche 2008-02-22 23:08:33 Re: Strange Authentication Problem
Previous Message Asche 2008-02-22 22:40:32 Strange Authentication Problem