Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 258E42E00F2 for ; Fri, 22 Feb 2008 19:00:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 80974-10 for ; Fri, 22 Feb 2008 19:00:03 -0400 (AST) X-Greylist: delayed 00:12:05.438358 by SQLgrey-1.7.5 Received: from epo-int1.asu.edu (epo-int1.asu.edu [129.219.187.20]) by postgresql.org (Postfix) with ESMTP id 1086B2E00C1 for ; Fri, 22 Feb 2008 19:00:08 -0400 (AST) Received: from djwd8wc1.dhcp.asu.edu ([129.219.57.174]) by epo-int1.asu.edu (Switch-3.1.8/Switch-3.1.7/asu-postoffice-prod) with ESMTP id m1MMl2E1024378 (version=TLSv1/SSLv3 cipher=RC4-SHA bits=128 verify=NO); Fri, 22 Feb 2008 15:47:33 -0700 From: David Bear Organization: College of Public Programs To: Tom Lane Subject: Re: creating column content from entry values Date: Fri, 22 Feb 2008 15:51:57 -0700 User-Agent: KMail/1.9.5 Cc: pgsql-admin@postgresql.org References: <200802211855.30789.david.bear@asu.edu> <20500.1203646658@sss.pgh.pa.us> In-Reply-To: <20500.1203646658@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200802221551.57262.david.bear@asu.edu> X-Virus-Scanned: by amavisd-new X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/291 X-Sequence-Number: 28365 On Thursday 21 February 2008 19:17, Tom Lane wrote: > David Bear 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