Please advise features in 7.1

"John Huttley" <John@mwk.co.nz> writes:

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),
);

You can do that now (and for many versions past) with a trigger.
It's not quite as convenient as it ought to be, but it's possible.
AFAIK there's no change in that situation for 7.1.

2. Any parameterised triggers

We've had parameterized triggers for years. Maybe you attach some
meaning to that term beyond what I do?

3. Any parameterised stored procedures that return a result set.

There is some support (dating back to Berkeley Postquel) for functions
returning sets, but it's pretty ugly and limited. Proper support might
happen in 7.2 ...

regards, tom lane

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "John Huttley" <John@mwk.co.nz>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, 23 November 2000 19:05
Subject: Re: [HACKERS] Please advise features in 7.1

"John Huttley" <John@mwk.co.nz> writes:

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),
);

You can do that now (and for many versions past) with a trigger.
It's not quite as convenient as it ought to be, but it's possible.
AFAIK there's no change in that situation for 7.1.

Yes, Perhaps defining the table with a dummy field and setting up a
'before'
trigger which replaced that field with a calculated value?

Messy but feasible.

2. Any parameterised triggers

We've had parameterized triggers for years. Maybe you attach some
meaning to that term beyond what I do?

I'm referring to the manual that says functions used for triggers must have
no parameters
and return a type Opaque. And indeed it is impossible to create a trigger
from a plSQL function that takes any parameters.

Thus if we have a lot of triggers which are very similar, we cannot just use
one function
and pass an identifying parameter or two to it. We must create an
individual function for each trigger.

Its irritating more than fatal.

3. Any parameterised stored procedures that return a result set.

There is some support (dating back to Berkeley Postquel) for functions
returning sets, but it's pretty ugly and limited. Proper support might
happen in 7.2 ...

Something to look forward to! Meanwhile I'll have a play and see if its
possible to use a read trigger
to populate a temporary table. hmm, that might require a statement level
trigger. Another thing for 7.2,
i guess.

The application programming we are doing now utilises stored procedures
returning record sets
(MSSQL) and the lack is showstopper in our migration plans. Sigh.

Thanks Tom

Regards

John

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "John Huttley" <John@mwk.co.nz>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, 23 November 2000 19:05
Subject: Re: [HACKERS] Please advise features in 7.1

"John Huttley" <John@mwk.co.nz> writes:

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),
);

You can do that now (and for many versions past) with a trigger.
It's not quite as convenient as it ought to be, but it's possible.
AFAIK there's no change in that situation for 7.1.

Yes, Perhaps defining the table with a dummy field and setting up a
'before'
trigger which replaced that field with a calculated value?

Messy but feasible.

2. Any parameterised triggers

We've had parameterized triggers for years. Maybe you attach some
meaning to that term beyond what I do?

I'm referring to the manual that says functions used for triggers must have
no parameters
and return a type Opaque. And indeed it is impossible to create a trigger
from a plSQL function that takes any parameters.

Thus if we have a lot of triggers which are very similar, we cannot just use
one function
and pass an identifying parameter or two to it. We must create an
individual function for each trigger.

Its irritating more than fatal.

3. Any parameterised stored procedures that return a result set.

There is some support (dating back to Berkeley Postquel) for functions
returning sets, but it's pretty ugly and limited. Proper support might
happen in 7.2 ...

Something to look forward to! Meanwhile I'll have a play and see if its
possible to use a read trigger
to populate a temporary table. hmm, that might require a statement level
trigger. Another thing for 7.2,
i guess.

The application programming we are doing now utilises stored procedures
returning record sets
(MSSQL) and the lack is showstopper in our migration plans. Sigh.

Thanks Tom

Regards

John

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

At 18:00 23/11/00 +1300, John Huttley wrote:

1. Calculated fields in table definitions . eg.

Can't really do this - you might want to consider a view with an insert &
update rule. I'm not sure how flexible rules are and you may not be able to
write rules to make views functions like tables, but that is at least part
of their purpose I think.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 06:00 PM 11/23/00 +1300, John Huttley wrote:

1. Calculated fields in table definitions . eg.

Create table test (
A Integer,
B integer,
the_sum As (A+B),
);

...

These are _extraordinarily_ useful for application development.

If anyone has a way of bolting on any of these to 7.0, I'd be keen to hear

from

you.

Create a trigger on insert/update for this case...

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

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 <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

"john huttley" <john@mwk.co.nz> writes:

We've had parameterized triggers for years. Maybe you attach some
meaning to that term beyond what I do?

I'm referring to the manual that says functions used for triggers must
have no parameters and return a type Opaque.

The function has to be declared that way, but you can actually pass a
set of string parameters to it from the CREATE TRIGGER command. The
strings show up in some special variable or other inside the function.
(No, I don't know why it was done in that ugly way...) See the manual's
discussion of trigger programming.

regards, tom lane

Thanks for your help, everyone.

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.
Implementing the create table syntax may not be too hard,
but not in 7.1...

2 Parameterised Triggers

Functionality is there, just that the documentation gave the wrong implication.
An user manual example of using parameterised triggers to implement referential
integrity
would be welcome.

3. Stored Procedures returning a record set.

Dream on!

Regards

John

On Tue, 28 Nov 2000, John Huttley wrote:

3. Stored Procedures returning a record set.

Dream on!

This is something I would be really interested to see working. What are the
issues? my understanding is that it is technically feasible but too
complicated to add to PL/PGsql? it seems to me a basic service that needs
to be implemented soon, even if its just returning multiple rows of one
column...

- Andrew

Hi,

how long is PG7.1 already in beta testing? can it be released before Christmas day?
can PG7.1 will recover database from system crash?

Thanks,

XuYifeng

Hi,

how long is PG7.1 already in beta testing? can it be released before Christmas day?
can PG7.1 will recover database from system crash?

Thanks,

XuYifeng

At 04:17 PM 11/28/00 +0800, xuyifeng wrote:

Hi,

how long is PG7.1 already in beta testing? can it be released before Christmas day?
can PG7.1 will recover database from system crash?

This guy's a troll from the PHP Builder's site (at least, Tim Perdue and I suspect this
due to some posts he made in regard to Tim's SourceForge/Postgres article).

Since he's read Tim's article, and at least some of the follow-up posts (given that
he's posted responses himself), he should know by now that PG 7.1 is still in a pre-beta
state and won't be released before Christmas day. I also posted a fairly long answer
to a question Tim's posted at phpbuilder.com regarding recoverability and this guy's
undoubtably read it, too.

Have I forgotten anything, xuyifeng?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

no doubt, I have touched some problems PG has, right? if PG is so good,
is there any necessary for the team to improve PG again?

Regards,
XuYifeng

----- Original Message -----
From: Don Baccus <dhogaza@pacifier.com>
To: xuyifeng <jamexu@telekbird.com.cn>; <pgsql-hackers@postgresql.org>
Sent: Tuesday, November 28, 2000 10:37 PM
Subject: Re: [HACKERS] beta testing version

At 11:15 PM 11/28/00 +0800, xuyifeng wrote:

no doubt, I have touched some problems PG has, right? if PG is so good,
is there any necessary for the team to improve PG again?

See? Troll...

The guy worships MySQL, just in case folks haven't made the connection.

I'm going to ignore him from now on, suggest others do the same, I'm sure
he'll go away eventually.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

you are complete wrong, if I don't like PG, I'll never go here or talk anything about PG, I don't care it.
I just want PG can be improved quickly, for me crash recover is very urgent problem,
otherewise PG is forced to stay on my desktop machine, We'll dare not move it to our Server,
I always see myself as a customer, customer is always right.

Regards,
XuYifeng

----- Original Message -----
From: Don Baccus <dhogaza@pacifier.com>
To: xuyifeng <jamexu@telekbird.com.cn>; <pgsql-hackers@postgresql.org>
Sent: Tuesday, November 28, 2000 11:16 PM
Subject: Re: [HACKERS] beta testing version

On Tue, Nov 28, 2000 at 02:04:01PM +1300, John Huttley wrote:

Thanks for your help, everyone.

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.

This can also be done with a trigger, which, while more cumbersome to
write, would be capable of doing the math at modification time.

Ross
--
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers
and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.

no doubt, I have touched some problems PG has, right? if PG is so good,
is there any necessary for the team to improve PG again?

*rofl*

Good call Don :)

- Thomas

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