Re: [GENERAL] How to refer to computed columns from other computed columns?
Alvaro Herrera írta: > Excerpts from Boszormenyi Zoltan's message of lun ago 16 14:45:07 -0400 2010: > >> Matthew Wilson írta: >> > > >>> I don't care if the code is rearranged so that c is replaced with an >>> inline definition during compilation. I'm not concerned about >>> efficiency here. I just don't want to have to redefine it manually over >>> and over again, because I know that as I update how c is defined, I'll >>> forget to update it everywhere. >>> >>> Maybe sql needs a preprocessing macro language like C. >>> >>> >> Or maybe we can dust off my GENERATED column patch >> I posted here in 2006. :-) >> > > Hmm, that seems entirely unrelated ... > What makes you think so? A generated column would put the work into INSERT and UPDATE statements, SELECTs would be faster and this way re-typing the same expression would be avoided. The generated column's definition is defined at one central place, with the type modifier on such a column in CREATE or ALTER TABLE , so the problem of the OP would be also solved. There was only one drawback, as Tom Lane pointed out a while back, but this was explicitely covered by the SQL standard at the time, it said that before triggers cannot look at the content of the generated columns. And with HOT and no indexes on the generated column, most of the bloat would also be avoided that comes from the extra internal UPDATE that such a column would introduce. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to refer to computed columns from other computed columns?
Excerpts from Boszormenyi Zoltan's message of lun ago 16 14:45:07 -0400 2010: > Matthew Wilson írta: > > I don't care if the code is rearranged so that c is replaced with an > > inline definition during compilation. I'm not concerned about > > efficiency here. I just don't want to have to redefine it manually over > > and over again, because I know that as I update how c is defined, I'll > > forget to update it everywhere. > > > > Maybe sql needs a preprocessing macro language like C. > > > > Or maybe we can dust off my GENERATED column patch > I posted here in 2006. :-) Hmm, that seems entirely unrelated ... -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to refer to computed columns from other computed columns?
2010/8/16 Boszormenyi Zoltan : > Matthew Wilson írta: >> On Mon Aug 16 10:26:36 2010, Tom Lane wrote: >> >>> Matthew Wilson writes: >>> All I can come up with so far is to use a view and then another view on top of that one: >>> Note that you don't actually need a view, as you can just write the >>> subselect in-line: >>> >>> select a, b, c, >>> case when c < 0 then 'no' >>> else 'yes' >>> end as d >>> from (select a, b, a - b as c from foo) as v1; >>> >>> This is the standard method for avoiding repeat calculations in SQL. >>> >>> One thing to keep in mind is that the planner will usually try to >>> "flatten" a nested sub-select (and whether it was written out manually >>> or pulled from a view does not matter here). This will result in the >>> sub-select's expressions getting inlined into the parent, so that the >>> calculations will actually get done more than once. If you're trying >>> to reduce execution time not just manual labor, you may want to put an >>> "offset 0" into the sub-select to create an optimization fence. But >>> test whether that really saves anything --- if there are bigger joins >>> or additional WHERE conditions involved, you can easily lose more than >>> you gain by preventing flattening. >>> >>> regards, tom lane >>> >>> >> >> Thanks so much for the help! >> >> I don't care if the code is rearranged so that c is replaced with an >> inline definition during compilation. I'm not concerned about >> efficiency here. I just don't want to have to redefine it manually over >> and over again, because I know that as I update how c is defined, I'll >> forget to update it everywhere. >> >> Maybe sql needs a preprocessing macro language like C. >> > > Or maybe we can dust off my GENERATED column patch > I posted here in 2006. :-) > > Best regards, > Zoltán Böszörményi > You mean this?: http://archives.postgresql.org/pgsql-hackers/2006-07/msg00543.php And this?: http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php And this?: http://archives.postgresql.org/pgsql-patches/2007-04/msg00107.php -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to refer to computed columns from other computed columns?
Matthew Wilson írta: > On Mon Aug 16 10:26:36 2010, Tom Lane wrote: > >> Matthew Wilson writes: >> >>> All I can come up with so far is to use a view and then another view on >>> top of that one: >>> >> Note that you don't actually need a view, as you can just write the >> subselect in-line: >> >> select a, b, c, >> case when c < 0 then 'no' >> else 'yes' >> end as d >> from (select a, b, a - b as c from foo) as v1; >> >> This is the standard method for avoiding repeat calculations in SQL. >> >> One thing to keep in mind is that the planner will usually try to >> "flatten" a nested sub-select (and whether it was written out manually >> or pulled from a view does not matter here). This will result in the >> sub-select's expressions getting inlined into the parent, so that the >> calculations will actually get done more than once. If you're trying >> to reduce execution time not just manual labor, you may want to put an >> "offset 0" into the sub-select to create an optimization fence. But >> test whether that really saves anything --- if there are bigger joins >> or additional WHERE conditions involved, you can easily lose more than >> you gain by preventing flattening. >> >> regards, tom lane >> >> > > Thanks so much for the help! > > I don't care if the code is rearranged so that c is replaced with an > inline definition during compilation. I'm not concerned about > efficiency here. I just don't want to have to redefine it manually over > and over again, because I know that as I update how c is defined, I'll > forget to update it everywhere. > > Maybe sql needs a preprocessing macro language like C. > Or maybe we can dust off my GENERATED column patch I posted here in 2006. :-) Best regards, Zoltán Böszörményi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to refer to computed columns from other computed columns?
On Mon Aug 16 10:26:36 2010, Tom Lane wrote: > Matthew Wilson writes: >> All I can come up with so far is to use a view and then another view on >> top of that one: > > Note that you don't actually need a view, as you can just write the > subselect in-line: > > select a, b, c, > case when c < 0 then 'no' > else 'yes' > end as d > from (select a, b, a - b as c from foo) as v1; > > This is the standard method for avoiding repeat calculations in SQL. > > One thing to keep in mind is that the planner will usually try to > "flatten" a nested sub-select (and whether it was written out manually > or pulled from a view does not matter here). This will result in the > sub-select's expressions getting inlined into the parent, so that the > calculations will actually get done more than once. If you're trying > to reduce execution time not just manual labor, you may want to put an > "offset 0" into the sub-select to create an optimization fence. But > test whether that really saves anything --- if there are bigger joins > or additional WHERE conditions involved, you can easily lose more than > you gain by preventing flattening. > > regards, tom lane > Thanks so much for the help! I don't care if the code is rearranged so that c is replaced with an inline definition during compilation. I'm not concerned about efficiency here. I just don't want to have to redefine it manually over and over again, because I know that as I update how c is defined, I'll forget to update it everywhere. Maybe sql needs a preprocessing macro language like C. Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to refer to computed columns from other computed columns?
Am 16.08.2010 14:45, schrieb Matthew Wilson: I'm converting some procedural code to SQL as an experiment. Here's the pseudocode: c = a - b if c< 0 then d = 'no' else d = 'yes' In SQL, I've got this: select a, b, a - b as c, case when a - b< 0 then 'no' else 'yes' end as d from foo; This is a trivial example, but you can see how I calculate a - b two separate times. In reality, I have much nastier calculations and they happen more than just twice. I'm looking for an elegant solution for this puzzle. I don't want to repeat that a - b part over and over because I likely will need to change how c gets defined and I don't want to have to change more than one place in the code. All I can come up with so far is to use a view and then another view on top of that one: create view v1 as select a, b, a - b as c from foo; create view v2 as select a, b, c, case when c< 0 then 'no' else 'yes' end as d from v1; This is better than the first solution because c is only defined in a single place. Is this the best possible solution? Thanks for the help. Matt You can also use the ' with Queries ' option to solve this Problem like this: with table_1 as (select a,b, a-b as c from foo) Select a,b, c, case when c<0 then 'no' else 'yes' end as d from table_1; I hope , it will help you -- Eric Ndengang Junior Datenbankentwickler Affinitas GmbH | Kohlfurter Straße 41/43 | 10999 Berlin | Germany email: eric.ndengang_fo...@affinitas.de | tel: +49.(0)30. 991 949 5 0 | www.edarling.de Geschäftsführer: Lukas Brosseder, David Khalil, Kai Rieke, Christian Vollmann Eingetragen beim Amtsgericht Berlin, HRB 115958 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to refer to computed columns from other computed columns?
Matthew Wilson writes: > All I can come up with so far is to use a view and then another view on > top of that one: Note that you don't actually need a view, as you can just write the subselect in-line: select a, b, c, case when c < 0 then 'no' else 'yes' end as d from (select a, b, a - b as c from foo) as v1; This is the standard method for avoiding repeat calculations in SQL. One thing to keep in mind is that the planner will usually try to "flatten" a nested sub-select (and whether it was written out manually or pulled from a view does not matter here). This will result in the sub-select's expressions getting inlined into the parent, so that the calculations will actually get done more than once. If you're trying to reduce execution time not just manual labor, you may want to put an "offset 0" into the sub-select to create an optimization fence. But test whether that really saves anything --- if there are bigger joins or additional WHERE conditions involved, you can easily lose more than you gain by preventing flattening. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to refer to computed columns from other computed columns?
I'm converting some procedural code to SQL as an experiment. Here's the pseudocode: c = a - b if c < 0 then d = 'no' else d = 'yes' In SQL, I've got this: select a, b, a - b as c, case when a - b < 0 then 'no' else 'yes' end as d from foo; This is a trivial example, but you can see how I calculate a - b two separate times. In reality, I have much nastier calculations and they happen more than just twice. I'm looking for an elegant solution for this puzzle. I don't want to repeat that a - b part over and over because I likely will need to change how c gets defined and I don't want to have to change more than one place in the code. All I can come up with so far is to use a view and then another view on top of that one: create view v1 as select a, b, a - b as c from foo; create view v2 as select a, b, c, case when c < 0 then 'no' else 'yes' end as d from v1; This is better than the first solution because c is only defined in a single place. Is this the best possible solution? Thanks for the help. Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
