Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
At 05:24 AM 11/30/00 +, Thomas Lockhart wrote: >> Is "if" clause support in PG? >> for example: >> "drop table aa if exist" >> "insert into aa values(1) if not exists select * from aa where i=1" > >No. afaict it is not in any SQL standard, so is unlikely to get much >attention from developers. The insert, at least, can be written in standard SQL anyway... - 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.
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
On Thu, 30 Nov 2000, Thomas Lockhart wrote: > > Is "if" clause support in PG? > > for example: > > "drop table aa if exist" > > "insert into aa values(1) if not exists select * from aa where i=1" > > No. afaict it is not in any SQL standard, so is unlikely to get much > attention from developers. Plus, for that second one can't you just do: INSERT INTO aa SELECT 1 WHERE NOT EXISTS (SELECT * FROM aa WHERE i=1); - Andrew
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
> Is "if" clause support in PG? > for example: > "drop table aa if exist" > "insert into aa values(1) if not exists select * from aa where i=1" No. afaict it is not in any SQL standard, so is unlikely to get much attention from developers. - Thomas
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
Is "if" clause support in PG? for example: "drop table aa if exist" "insert into aa values(1) if not exists select * from aa where i=1" I would like PG support it. --- XuYifeng - Original Message - From: John Huttley <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 9:04 AM Subject: [HACKERS] Please advise features in 7.1 (SUMMARY) > 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 > > >
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
From: "Ross J. Reedstrom" <[EMAIL PROTECTED]> > On Tue, Nov 28, 2000 at 05:19:45PM +0100, Zeugswetter Andreas SB wrote: > > > 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. > > > > > > As I said in my original post, my understanding of computed fields may > be in error. If they're computed at SELECT time, to avoid creating table > space, then a VIEW is exacly the right solution. However, it's easy to > come up with examples of complex calculations that it would be useful > to cache the results of, in the table. Then, computing at INSERT/UPDATE > is clearly the way to go. > > So, having _both_ is the best thing. > > Ross > I'm new at this, but the view thing? Isn't that just the same as: create table test2 ( i1 int4, i2 int4); ...insert... select i1,i2,i1+i2 from test2; Magnus -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Programmer/Networker [|] Magnus Naeslund PGP Key: http://www.genline.nu/mag_pgp.txt -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
AW: [HACKERS] Please advise features in 7.1 (SUMMARY)
> > 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
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
> 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 -Mitch
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
On Tue, Nov 28, 2000 at 05:19:45PM +0100, Zeugswetter Andreas SB wrote: > > 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. > As I said in my original post, my understanding of computed fields may be in error. If they're computed at SELECT time, to avoid creating table space, then a VIEW is exacly the right solution. However, it's easy to come up with examples of complex calculations that it would be useful to cache the results of, in the table. Then, computing at INSERT/UPDATE is clearly the way to go. So, having _both_ is the best thing. Ross
AW: [HACKERS] Please advise features in 7.1 (SUMMARY)
> 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
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
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. Just playing the devil's advocate a bit. -Mitch - Original Message - From: "Zeugswetter Andreas SB" <[EMAIL PROTECTED]> To: "'Ross J. Reedstrom'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 28, 2000 7:50 AM Subject: AW: [HACKERS] Please advise features in 7.1 (SUMMARY) > > > > 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 >
AW: [HACKERS] Please advise features in 7.1 (SUMMARY)
> > 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: [HACKERS] Please advise features in 7.1 (SUMMARY)
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.
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
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
[HACKERS] Please advise features in 7.1 (SUMMARY)
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
Re: [HACKERS] Please advise features in 7.1
"john huttley" <[EMAIL PROTECTED]> 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
Re: [HACKERS] Please advise features in 7.1
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 <[EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: AW: [HACKERS] Please advise features in 7.1
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.
Re: [HACKERS] Please advise features in 7.1
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 |/
AW: [HACKERS] Please advise features in 7.1
> 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
Re: [HACKERS] Please advise features in 7.1
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "John Huttley" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, 23 November 2000 19:05 Subject: Re: [HACKERS] Please advise features in 7.1 > "John Huttley" <[EMAIL PROTECTED]> 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
Re: [HACKERS] Please advise features in 7.1
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "John Huttley" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, 23 November 2000 19:05 Subject: Re: [HACKERS] Please advise features in 7.1 > "John Huttley" <[EMAIL PROTECTED]> 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
Re: [HACKERS] Please advise features in 7.1
"John Huttley" <[EMAIL PROTECTED]> 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
[HACKERS] Please advise features in 7.1
Hello, I've looked at the resources available through the web page to CVS and other stuff, however I cant find a statement of whats likely to be in 7.1 and what is planned for later. 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 like MSSQL 2. Any parameterised triggers 3. Any parameterised stored procedures that return a result set. 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. Regards John
