Skip site navigation (1) Skip section navigation (2)

Re: creating column content from entry values

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Bear <david(dot)bear(at)asu(dot)edu>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: creating column content from entry values
Date: 2008-02-22 02:17:38
Message-ID: 20500.1203646658@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
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.
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

In response to

Responses

pgsql-admin by date

Next:From: Wolfgang RodriguezDate: 2008-02-22 04:43:17
Subject: Clave usuario postgres
Previous:From: David BearDate: 2008-02-22 01:55:30
Subject: creating column content from entry values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group