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

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 (view raw or flat)
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

pgsql-admin by date

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

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