pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
creating column content from entry values
3+ messages / 2 participants
[nested] [flat]

* creating column content from entry values
@ 2008-02-22 01:55 David Bear <david.bear@asu.edu>
  2008-02-22 02:17 ` Re: creating column content from entry values Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: David Bear @ 2008-02-22 01:55 UTC (permalink / raw)
  To: pgsql-admin

I am too new to this environment to know what to call what I want to do.

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. 
The function may be a simple as a concatenation, or I may want to do other 
things to manipulate the strings that are entered. I think what I want is a 
function that I can call when I do the insert like this

insert into mytable (col1, col2, col3) values ('somestring', 'another 
string' )

but have the insert fix col3 to be whatever it the function computes it to be.

does this make any sense?

 
--
David Bear
College of Public Programs/ASU
411 N Central, Phoenix, AZ 85004



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: creating column content from entry values
  2008-02-22 01:55 creating column content from entry values David Bear <david.bear@asu.edu>
@ 2008-02-22 02:17 ` Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-22 22:51   ` Re: creating column content from entry values David Bear <david.bear@asu.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2008-02-22 02:17 UTC (permalink / raw)
  To: David Bear <david.bear@asu.edu>; +Cc: pgsql-admin

David Bear <david.bear@asu.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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: creating column content from entry values
  2008-02-22 01:55 creating column content from entry values David Bear <david.bear@asu.edu>
  2008-02-22 02:17 ` Re: creating column content from entry values Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-22 22:51   ` David Bear <david.bear@asu.edu>
  0 siblings, 0 replies; 3+ messages in thread

From: David Bear @ 2008-02-22 22:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2008-02-22 22:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-22 01:55 creating column content from entry values David Bear <david.bear@asu.edu>
2008-02-22 02:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-22 22:51   ` David Bear <david.bear@asu.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox