AW: [HACKERS] Please advise features in 7.1 (SUMMARY)

[Zeugswetter Andreas SB]

> > So, having _both_ is the best thing.
> 
> Absolutely, that's always what I meant -- we already have views and views
> can do this type of stuff at SELECT time can't they? So it's not a change,
> just an addition

And the precalculated and stored on disk thing can be done with triggers.

Andreas

AW: [HACKERS] Please advise features in 7.1 (SUMMARY)

[Zeugswetter Andreas SB]

> I guess it depends on what you're using it for -- disk space 
> is cheap and
> abundant anymore, I can see some advantages of having it 
> computed only once
> rather than X times, where X is the number of SELECTs as that 
> could get
> costly on really high traffic servers.. Costly not so much for simple
> computations like that but more complex ones.

Once and for all forget the argument in database technology, that disk space 
is cheap in regard to $/Mb. That is not the question. The issue is:
1. amout of rows you can cache
2. number of rows you can read from disk per second
 (note that it is not pages/sec)
3. how many rows you can sort in memory

In the above sence disk space is one of the most expensive things in a
database system. Saving disk space where possible will gain you drastic
performance advantages.

Andreas

AW: [HACKERS] Please advise features in 7.1 (SUMMARY)

[Zeugswetter Andreas SB]

> > This is a summary of replies.
> > 
> > 1. Calculated fields in table definitions . eg.
> > 
> > Create table test (
> >  A Integer,
> >  B integer,
> > the_sum   As  (A+B),
> > );
> > 
> > This functionality can be achieved through the use of views.
> 
> Using a view for this isn't quite the same functionality as a computed
> field, from what I understand, since the calculation will be done at
> SELECT time, rather than INSERT/UPDATE.

I would expect the calculated field from above example to be calculated
during select time also, no ? You don't want to waste disk space with something 
you can easily compute at runtime.

Andreas

Re: AW: [HACKERS] Please advise features in 7.1

[Don Baccus]

At 12:28 PM 11/23/00 +0100, Zeugswetter Andreas SB wrote:
>
>> Reason: I want to know if any of these features are scheduled.
>> 
>> 1. Calculated fields in table definitions . eg.
>> 
>>Create table test (
>> A Integer,
>> B integer,
>>the_sum   As  (A+B),
>> );
>
>This is currently easily done with a procedure that takes a tabletype
parameter
>with the name the_sum returning the sum of a + b.
>
>   Create table test (
> A Integer,
> B integer
>);
>
>create function the_sum (test) returns integer as
>'
>   begin;
>   return ($1.a + $1.b);
>   end;
>' language 'plpgsql';
>
>A select * won't return the_sum

create view test2 select A, B, A+B as the_sum from test;

will, though.

See, lots of ways to do it!



- Don Baccus, Portland OR <[EMAIL PROTECTED]>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

AW: [HACKERS] Please advise features in 7.1

[Zeugswetter Andreas SB]

> Reason: I want to know if any of these features are scheduled.
> 
> 1. Calculated fields in table definitions . eg.
> 
>Create table test (
> A Integer,
> B integer,
>the_sum   As  (A+B),
> );

This is currently easily done with a procedure that takes a tabletype parameter
with the name the_sum returning the sum of a + b.

   Create table test (
 A Integer,
 B integer
);

create function the_sum (test) returns integer as
'
begin;
return ($1.a + $1.b);
end;
' language 'plpgsql';

A select * won't return the_sum, but a 
select t.a, t.b, t.the_sum from test t; 
will do what you want.

Unfortunately it only works if you qualify the column the_sum with a tablename or 
alias.
(But I heard you mention the Micro$oft word, and they tend to always use aliases 
anyway)
Maybe we could even extend the column search in the unqualified case ?

Andreas