Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id DCF632E00F6 for ; Thu, 21 Feb 2008 22:17:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51559-06 for ; Thu, 21 Feb 2008 22:17:47 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 739C72E0114 for ; Thu, 21 Feb 2008 22:17:45 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1M2Hc8J020501; Thu, 21 Feb 2008 21:17:38 -0500 (EST) To: David Bear cc: pgsql-admin@postgresql.org Subject: Re: creating column content from entry values In-reply-to: <200802211855.30789.david.bear@asu.edu> References: <200802211855.30789.david.bear@asu.edu> Comments: In-reply-to David Bear message dated "Thu, 21 Feb 2008 18:55:30 -0700" Date: Thu, 21 Feb 2008 21:17:38 -0500 Message-ID: <20500.1203646658@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/264 X-Sequence-Number: 28338 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. 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