Thread: RFE: Column aliases in WHERE clauses
It would be nice if PostgreSQL supported column aliases in WHERE clauses, eg: SELECT left(value, 1) AS first_letter FROM some_table WHERE first_letter > 'a'; Is this the proper mailing list for such feature requests? Thanks in advance, Daniel Serodio
On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: > It would be nice if PostgreSQL supported column aliases in WHERE > clauses, eg: > > SELECT left(value, 1) AS first_letter > FROM some_table > WHERE first_letter > 'a'; > > Is this the proper mailing list for such feature requests? I think this is explicitly disallowed by the spec. And by Tom: http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php > > Thanks in advance, > Daniel Serodio > -Ryan Kelly
On Sep 17, 2012, at 17:42, Ryan Kelly <rpkelly22@gmail.com> wrote: > On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: >> It would be nice if PostgreSQL supported column aliases in WHERE >> clauses, eg: >> >> SELECT left(value, 1) AS first_letter >> FROM some_table >> WHERE first_letter > 'a'; >> >> Is this the proper mailing list for such feature requests? > I think this is explicitly disallowed by the spec. > Just to be clear, the spec does not care where you post your feature requests...it is the feature that it disallows. When in doubt the general list is a good choice and all the key people monitor it and will move the discussion elsewhereif warranted. David J.
Ryan Kelly wrote:
Regards,
Daniel Serodio
Tom's explanation makes perfect sense, thanks for the pointer.On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote:It would be nice if PostgreSQL supported column aliases in WHERE clauses, eg: SELECT left(value, 1) AS first_letter FROM some_table WHERE first_letter > 'a'; Is this the proper mailing list for such feature requests?I think this is explicitly disallowed by the spec. And by Tom: http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php
Regards,
Daniel Serodio
On Mon, Sep 17, 2012 at 3:10 PM, Daniel Serodio (lists) <daniel.lists@mandic.com.br> wrote: > Ryan Kelly wrote: > > On Mon, Sep 17, 2012 at 06:18:53PM -0300, Daniel Serodio (lists) wrote: > > It would be nice if PostgreSQL supported column aliases in WHERE > clauses, eg: > > SELECT left(value, 1) AS first_letter > FROM some_table > WHERE first_letter > 'a'; > > Is this the proper mailing list for such feature requests? > > I think this is explicitly disallowed by the spec. > > And by Tom: > http://archives.postgresql.org/pgsql-general/2002-11/msg01411.php > > Tom's explanation makes perfect sense, thanks for the pointer. This definitely makes sense in the context of aggregation, but I'm wondering if the same argument applies in the use case originally posted: SELECT left(value, 1) as first_letter FROM some_table WHERE first_letter > 'a'; Obviously, you can write this as: SELECT left(value, 1) as first_letter FROM some_table WHERE left(value, 1) > 'a'; This would run fine, though you'd be doing a sequential scan on the entire table, getting the left most character in each value, then filtering those results. This of course assumes you haven't built an index on left(value, 1). Thus, in theory the compiler *could* resolve the actual definition of first_letter and substitute in that expression on the fly. I'm wondering if that concept is actually disallowed by the SQL spec. Obviously, it would add complexity (and compile overhead) but would be somewhat handy to avoid repeating really complicated expressions. Perhaps Common Table Expressions are a better way of doing this thing anyhow. Mike
Mike Christensen <mike@kitchenpc.com> writes: > This definitely makes sense in the context of aggregation, but I'm > wondering if the same argument applies in the use case originally > posted: > SELECT left(value, 1) as first_letter > FROM some_table > WHERE first_letter > 'a'; > Obviously, you can write this as: > SELECT left(value, 1) as first_letter > FROM some_table > WHERE left(value, 1) > 'a'; > This would run fine, though you'd be doing a sequential scan on the > entire table, getting the left most character in each value, then > filtering those results. This of course assumes you haven't built an > index on left(value, 1). > Thus, in theory the compiler *could* resolve the actual definition of > first_letter and substitute in that expression on the fly. I'm > wondering if that concept is actually disallowed by the SQL spec. Yes, it is. If you read the spec you'll find that the scope of visibility of names defined in the SELECT list doesn't include WHERE. It's easier to understand why this is if you realize that SQL has a very clear model of a "pipeline" of query execution. Conceptually, what happens is: 1. Form the cartesian product of the tables listed in FROM (ie, all combinations of rows). 2. Apply the WHERE condition to each row from 1, and drop rows that don't pass it. 3. If there's a GROUP BY, merge the surviving rows into groups. 4. If there's aggregate functions, compute those over the rows in each group. 5. If there's a HAVING, filter the grouped rows according to that. 6. Evaluate the SELECT expressions for each remaining row. 7. If there's an ORDER BY, evaluate those expressions and sort the remaining rows accordingly. (Obviously, implementations try to improve on this - you don't want to actually form the cartesian product - but that's the conceptual model.) The traditional shortcut of doing "ORDER BY select-column-reference" is okay according to this world view, because the SELECT expressions are already available when ORDER BY needs them. However, it's not sensible to refer to SELECT outputs in WHERE, HAVING, or GROUP BY, because those steps precede the evaluation of the SELECT expressions. This isn't just academic nit-picking either, because the SELECT expressions might not be valid for rows that don't pass WHERE etc. Consider SELECT 1/x AS inverse FROM data WHERE x <> 0; The implementation *must* apply WHERE before computing the SELECT expressions, or it'll get zero-divide failures that should not happen. Now, having said all that, if you try it you'll find that Postgres does allow select column references in GROUP BY, using the model you propose above of copying whatever expression is in SELECT into GROUP BY. This is, to put it politely, a mistake that we are now stuck with for backwards-compatibility reasons. It's not spec compliant and it doesn't fit the language's conceptual model, but it's been that way for long enough that we're not likely to take it out. We are not, however, gonna introduce the same mistake elsewhere. > Obviously, it would add complexity (and compile overhead) but would be > somewhat handy to avoid repeating really complicated expressions. > Perhaps Common Table Expressions are a better way of doing this thing > anyhow. CTEs or sub-selects are a better answer for that. Each sub-select has its own instance of the conceptual pipeline. regards, tom lane
On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Mike Christensen <mike@kitchenpc.com> writes: >> This definitely makes sense in the context of aggregation, but I'm >> wondering if the same argument applies in the use case originally >> posted: > >> SELECT left(value, 1) as first_letter >> FROM some_table >> WHERE first_letter > 'a'; > >> Obviously, you can write this as: > >> SELECT left(value, 1) as first_letter >> FROM some_table >> WHERE left(value, 1) > 'a'; > >> This would run fine, though you'd be doing a sequential scan on the >> entire table, getting the left most character in each value, then >> filtering those results. This of course assumes you haven't built an >> index on left(value, 1). > >> Thus, in theory the compiler *could* resolve the actual definition of >> first_letter and substitute in that expression on the fly. I'm >> wondering if that concept is actually disallowed by the SQL spec. > > Yes, it is. If you read the spec you'll find that the scope of > visibility of names defined in the SELECT list doesn't include WHERE. > > It's easier to understand why this is if you realize that SQL has a very > clear model of a "pipeline" of query execution. Conceptually, what > happens is: > > 1. Form the cartesian product of the tables listed in FROM (ie, all > combinations of rows). > > 2. Apply the WHERE condition to each row from 1, and drop rows that > don't pass it. > > 3. If there's a GROUP BY, merge the surviving rows into groups. > > 4. If there's aggregate functions, compute those over the rows in > each group. > > 5. If there's a HAVING, filter the grouped rows according to that. > > 6. Evaluate the SELECT expressions for each remaining row. > > 7. If there's an ORDER BY, evaluate those expressions and sort the > remaining rows accordingly. > > (Obviously, implementations try to improve on this - you don't want > to actually form the cartesian product - but that's the conceptual > model.) > > The traditional shortcut of doing "ORDER BY select-column-reference" > is okay according to this world view, because the SELECT expressions > are already available when ORDER BY needs them. However, it's not > sensible to refer to SELECT outputs in WHERE, HAVING, or GROUP BY, > because those steps precede the evaluation of the SELECT expressions. > > This isn't just academic nit-picking either, because the SELECT > expressions might not be valid for rows that don't pass WHERE etc. > Consider > SELECT 1/x AS inverse FROM data WHERE x <> 0; > The implementation *must* apply WHERE before computing the SELECT > expressions, or it'll get zero-divide failures that should not happen. > > Now, having said all that, if you try it you'll find that Postgres > does allow select column references in GROUP BY, using the model > you propose above of copying whatever expression is in SELECT into > GROUP BY. This is, to put it politely, a mistake that we are now > stuck with for backwards-compatibility reasons. It's not spec compliant > and it doesn't fit the language's conceptual model, but it's been that > way for long enough that we're not likely to take it out. We are not, > however, gonna introduce the same mistake elsewhere. > >> Obviously, it would add complexity (and compile overhead) but would be >> somewhat handy to avoid repeating really complicated expressions. >> Perhaps Common Table Expressions are a better way of doing this thing >> anyhow. > > CTEs or sub-selects are a better answer for that. Each sub-select has > its own instance of the conceptual pipeline. Excellent information, Tom! I've been somewhat curious on this behavior for some time now, and it's great to get a detailed answer.. Mike
On 09/18/2012 07:32 AM, Tom Lane wrote: > It's easier to understand why this is if you realize that SQL has a very > clear model of a "pipeline" of query execution. I just wish they hadn't written it backwards! It'd be much less confusing were it formulated as something like: SELECT FROM thetable WHERE first_letter > 'a' RESULTS left(value,1) AS first_letter or something, where the order is more obvious. I really dislike the way SQL is written not-quite-backwards. -- Craig Ringer
On Mon, 2012-09-17 at 16:44 -0700, Mike Christensen wrote: > On Mon, Sep 17, 2012 at 4:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Mike Christensen <mike@kitchenpc.com> writes: [-----------] > > It's easier to understand why this is if you realize that SQL has a very > > clear model of a "pipeline" of query execution. Conceptually, what > > happens is: > > > > 1. Form the cartesian product of the tables listed in FROM (ie, all > > combinations of rows). > > > > 2. Apply the WHERE condition to each row from 1, and drop rows that > > don't pass it. > > > > 3. If there's a GROUP BY, merge the surviving rows into groups. > > > > 4. If there's aggregate functions, compute those over the rows in > > each group. > > > > 5. If there's a HAVING, filter the grouped rows according to that. > > > > 6. Evaluate the SELECT expressions for each remaining row. > > > > 7. If there's an ORDER BY, evaluate those expressions and sort the > > remaining rows accordingly. > > > > (Obviously, implementations try to improve on this - you don't want > > to actually form the cartesian product - but that's the conceptual > > model.) > > > > The traditional shortcut of doing "ORDER BY select-column-reference" > > is okay according to this world view, because the SELECT expressions [--------------] > > are already available when ORDER BY needs them. However, it's not > > sensible to refer to SELECT outputs in WHERE, HAVING, or GROUP BY, > > because those steps precede the evaluation of the SELECT expressions. > > > > This isn't just academic nit-picking either, because the SELECT > > expressions might not be valid for rows that don't pass WHERE etc. > > Consider > > SELECT 1/x AS inverse FROM data WHERE x <> 0; > > The implementation *must* apply WHERE before computing the SELECT > > expressions, or it'll get zero-divide failures that should not happen. [-----------------] > > Excellent information, Tom! I've been somewhat curious on this > behavior for some time now, and it's great to get a detailed answer.. > Yes. But it puzzles me, if it *conceptually* would be a signifficant misstake, when what Tom calls "select-column-reference" (I understand as: the colunm name introduced on the select-list), would actually be regarded by the SQL parser as "macro-definition". Just to place the *string*, defined at select-list-level by "AS <name>", to wherever it's used in the WHERE/ORDER/GROUP or HAVING clauses at earlier then select-list-evaluation processing stage. Actual Tom's example(1): SELECT 1/x AS inverse FROM data WHERE x <> 0; extended to (2): SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20; could be written by user as (3): SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20; but token/replaced to its form (2) before WHERE evaluation. -R
On Tue, Sep 18, 2012 at 4:44 PM, Craig Ringer <ringerc@ringerc.id.au> wrote: > On 09/18/2012 07:32 AM, Tom Lane wrote: >> >> It's easier to understand why this is if you realize that SQL has a very >> clear model of a "pipeline" of query execution. > > I just wish they hadn't written it backwards! > > It'd be much less confusing were it formulated as something like: > > SELECT > FROM thetable > WHERE first_letter > 'a' > RESULTS left(value,1) AS first_letter > > or something, where the order is more obvious. I really dislike the way SQL > is written not-quite-backwards. I agree, SQL has its quirks. There are general principles, but they're all violated somewhere... "UNION ALL" annoys me some by breaking the rule that more words = more work, which is almost universally adhered to elsewhere. But such is the nature of specifications. ChrisA
>>>>> "Craig" == Craig Ringer <ringerc@ringerc.id.au> writes: Craig> I just wish they hadn't written it backwards! Craig> It'd be much less confusing were it formulated as something Craig> like: Craig> SELECT FROM thetable WHERE first_letter > 'a' RESULTS Craig> left(value,1) AS first_letter Craig> or something, where the order is more obvious. I really Craig> dislike the way SQL is written not-quite-backwards. It's not "written backwards", it's plain natural language semantics: "give me the first letter of all records where the first letter is greater than a". Refining a set is better done walking from the more general set to a subset, not the other way around, IMO: "give me all persons that are females and over the age of 20". Mathematical set builder notation does this in a similar fashion, for the same reason. -- Eden Cardim http://insoli.de
On Tue, Sep 18, 2012 at 2:53 AM, Eden Cardim <eden@insoli.de> wrote: >>>>>> "Craig" == Craig Ringer <ringerc@ringerc.id.au> writes: > > Craig> I just wish they hadn't written it backwards! > > Craig> It'd be much less confusing were it formulated as something > Craig> like: > > Craig> SELECT FROM thetable WHERE first_letter > 'a' RESULTS > Craig> left(value,1) AS first_letter > > Craig> or something, where the order is more obvious. I really > Craig> dislike the way SQL is written not-quite-backwards. > > It's not "written backwards", it's plain natural language semantics: > "give me the first letter of all records where the first letter is > greater than a". Refining a set is better done walking from the more > general set to a subset, not the other way around, IMO: "give me all > persons that are females and over the age of 20". Mathematical set > builder notation does this in a similar fashion, for the same reason. Oh no, this debate again.. I do admit LINQ kind of threw me for a loop as they took the other approach (from f in foo where f.id > 5 select f), which makes you think about the collection you're working with first. I usually think about the table first when I'm writing a query. I can also say if the table came before the columns, we'd probably have a lot more SQL editors with auto-complete that worked :) Mike
On Tue, Sep 18, 2012 at 11:20 AM, Eden Cardim <eden@insoli.de> wrote: >>>>>> "Mike" == Mike Christensen <mike@kitchenpc.com> writes: > > Mike> I can also say if the table came before the columns, we'd > Mike> probably have a lot more SQL editors with auto-complete that > Mike> worked :) > > There's nothing stopping an editor from making you type the table > first though, it's easier to implement that in an editor than it is to > change the way people have been thinking about math for the last few > of centuries. No, I meant editors that auto-complete SQL statements for you as you're typing them. Like Intellisense in Visual Studio. Obviously you wouldn't want to type "select " and then see a list of every column in your database, since it doesn't know what table you want yet. Mike
>>>>> "Mike" == Mike Christensen <mike@kitchenpc.com> writes: Mike> I can also say if the table came before the columns, we'd Mike> probably have a lot more SQL editors with auto-complete that Mike> worked :) There's nothing stopping an editor from making you type the table first though, it's easier to implement that in an editor than it is to change the way people have been thinking about math for the last few of centuries.
>>>>> "Mike" == Mike Christensen <mike@kitchenpc.com> writes: Mike> No, I meant editors that auto-complete SQL statements for Mike> you as you're typing them. Like Intellisense in Visual Mike> Studio. Mike> Obviously you wouldn't want to type "select " and then see a Mike> list of every column in your database, since it doesn't know Mike> what table you want yet. Again, you can type "select" and the have the editor expand the whole thing into "select _ from [cursor is now here]" and present you with a list of tables for the autocomplete, then after you've selected the table, it goes back to the field part of the query. I have emacs rigged to do this with yasnippet, except that it relies on a handwritten function, not intellisense. Point stands, easier done in a single editor as opposed to changing the semantics of math.
On Tue, Sep 18, 2012 at 2:53 AM, Eden Cardim <eden@insoli.de> wrote:
>>>>> "Craig" == Craig Ringer <ringerc@ringerc.id.au> writes:
Craig> I just wish they hadn't written it backwards!
Craig> It'd be much less confusing were it formulated as something
Craig> like:
Craig> SELECT FROM thetable WHERE first_letter > 'a' RESULTS
Craig> left(value,1) AS first_letter
Craig> or something, where the order is more obvious. I really
Craig> dislike the way SQL is written not-quite-backwards.
It's not "written backwards", it's plain natural language semantics:
"give me the first letter of all records where the first letter is
greater than a". Refining a set is better done walking from the more
general set to a subset, not the other way around, IMO: "give me all
persons that are females and over the age of 20". Mathematical set
builder notation does this in a similar fashion, for the same reason.
Natural language semantics will get you into trouble though. After all, I think Lisp follows natural language semantics remarkably closely if your natural language is Irish Gaelic....
Best Wishes,
Chris Travers
Hi There, I've snipped a piece for the daily digest because I take issue with what's asserted here as a reason for not allowing aliasesin where clauses. << snip This isn't just academic nit-picking either, because the SELECT expressions might not be valid for rows that don't pass WHERE etc. Consider SELECT 1/x AS inverse FROM data WHERE x <> 0; The implementation *must* apply WHERE before computing the SELECT expressions, or it'll get zero-divide failures that should not happen. end snip>> Irrespective of whether the standard prohibits aliases in where clauses, the reasoning here is irrelevant to the discussionat hand. If I say: SELECT 1/x AS inverse FROM data WHERE x <> 0 or inverse > 0.5 (for arguments sake) (in a SQL dialect that supports it) then I must expect inverse to be evaluated for every row, exactly as if I said: SELECT 1/x AS inverse FROM data WHERE x <> 0 or 1/x > 0.5 It's surely not the role of the standard to protect us from the consequences of our own folly. Since some dialects support the idiom and others don't there can't be any compelling reason to withhold support. It's reallya matter of style. For my money the DRY style is better. -- Steve Haresnape Creative Integrity Ltd
On 19/09/2012 04:57, Chris Travers wrote: > > Natural language semantics will get you into trouble though. After all, > I think Lisp follows natural language semantics remarkably closely if > your natural language is Irish Gaelic.... Really? I haven't used Irish seriously since I left school - maybe I should learn Lisp.... :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 09/19/2012 02:36 PM, Steve Haresnape wrote: > Since some dialects support the idiom and others don't there can't be > any compelling reason to withhold support. It's really a matter of style. > For my money the DRY style is better. So you're saying that you want the implementation to effectively clone the aliased SELECT term into the WHERE clause? If so, what about functions with side-effects? -- Craig Ringer
On Wed, 2012-09-19 at 20:36 +0800, Craig Ringer wrote: > On 09/19/2012 02:36 PM, Steve Haresnape wrote: > > > Since some dialects support the idiom and others don't there can't be > > any compelling reason to withhold support. It's really a matter of style. > > For my money the DRY style is better. > > So you're saying that you want the implementation to effectively clone > the aliased SELECT term into the WHERE clause? > > If so, what about functions with side-effects? What about them: if they are put in the where clause by the user - e.g. not from unaliasing? Just bug-traceing will be more difficult from alias obfuscating effects. That's all. -R
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Steve Haresnape > Sent: Wednesday, September 19, 2012 2:37 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] RFE: Column aliases in WHERE clauses > > Hi There, > > I've snipped a piece for the daily digest because I take issue with what's > asserted here as a reason for not allowing aliases in where clauses. > > << snip This isn't just academic nit-picking either, because the SELECT > expressions might not be valid for rows that don't pass WHERE etc. > Consider > SELECT 1/x AS inverse FROM data WHERE x <> 0; The implementation > *must* apply WHERE before computing the SELECT > expressions, or it'll get zero-divide failures that should not happen. end > snip>> > > Irrespective of whether the standard prohibits aliases in where clauses, the > reasoning here is irrelevant to the discussion at hand. > > If I say: > SELECT 1/x AS inverse FROM data WHERE x <> 0 or inverse > 0.5 (for > arguments sake) (in a SQL dialect that supports it) then I must expect inverse > to be evaluated for every row, exactly as if I said: > SELECT 1/x AS inverse FROM data WHERE x <> 0 or 1/x > 0.5 > > It's surely not the role of the standard to protect us from the consequences > of our own folly. > > Since some dialects support the idiom and others don't there can't be any > compelling reason to withhold support. It's really a matter of style. > For my money the DRY style is better. > There are any number of idioms that other dialects (and even PostgreSQL) support for compatibility or standards reasons that, if decided upon now, would not be included. The SQL execution model is, from what I can infer, single-pass and linear and while necessitating sometimes verbose syntax it makes execution considerably less-problematic and more deterministic by the simple fact that column names are more narrowly scoped. While I can and have seen situations where such a feature would be handy working around it is not that difficult. For really complex expressions coding the formula into a (ideally immutable) function is a better solution anyway. As to Tom's example its main implication is that the WHERE-clause has to be evaluated before the SELECT-list in the single-pass linear model. That is quite relevant if not the MAIN point of the example. I could maybe see something like the following having some value: SELECT inverse FROM data WHERE x<>0 AND inverse > .5 MACRO inverse (1/x) Apart from all this I'll simply say that because the feature itself has value it is the means of implementation that needs to be discussed and not the merits of the feature itself. Since no new capabilities are being added, just verbosity reduction, the hurdle to spend development time on this is pretty darn high. To phrase it differently I do not believe that the core team would outright reject the idea of making aliases work IF they were presented with a suitable implementation; it just sounds like they are "withholding support" to the extent that they have not been convinced to do the work themselves. As an outsider I can understand, and in this case agree with, that position. David J.
On Wed, Sep 19, 2012 at 11:15 PM, David Johnston <polobo@yahoo.com> wrote: > I could maybe see something like the following having some value: > > SELECT inverse > FROM data > WHERE x<>0 AND inverse > .5 > MACRO inverse (1/x) > WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM macros WHERE x<>0 AND inverse > .5 ChrisA
> > On Wed, Sep 19, 2012 at 11:15 PM, David Johnston <polobo@yahoo.com> > wrote: > > I could maybe see something like the following having some value: > > > > SELECT inverse > > FROM data > > WHERE x<>0 AND inverse > .5 > > MACRO inverse (1/x) > > > > WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM > macros WHERE x<>0 AND inverse > .5 > In your example the "macro" has to either be attached directly to the FROM or be used as part of a sub-select; it is not a text substitution macro at all. The pre-processor upon encountering a macro, would simply replace all identifiers (at the same level in the query) with "(expression)". David J.
On 2012-09-18, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote: > > Actual Tom's example(1): > SELECT 1/x AS inverse FROM data WHERE x <> 0; > extended to (2): > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20; > could be written by user as (3): > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20; > but token/replaced to its form (2) before WHERE evaluation. Macros are confusing: select random()*10 as confusion from generate_series(1,10) where confusion > 5; -- ⚂⚃ 100% natural
On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
On 2012-09-18, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:Macros are confusing:
>
> Actual Tom's example(1):
> SELECT 1/x AS inverse FROM data WHERE x <> 0;
> extended to (2):
> SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20;
> could be written by user as (3):
> SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20;
> but token/replaced to its form (2) before WHERE evaluation.
select random()*10 as confusion from generate_series(1,10)
where confusion > 5;
Also you can already do this:
CREATE FUNCTION inverse(data) RETURNS NUMERIC LANGUAGE SQL IMMUTABLE AS $$
select case when $1.x = 0 then null else 1/$1.x end;
$$;
Then it can be used as a macro:
SELECT d.inverse FROM data d WHERE d.x <> 0 AND d.inverse > 0.5;
Wondering if we want to support something like this, essentially anonymous functions, if we shouldn't extend the WITH clause to support something like WITH FUNCTION for cases where you don't want your macro to persist.
I don't know though. Are there cases where you don't want the macro to persist?
Best Wishes,
Chris Travers
On Sat, 2012-09-22 at 20:00 -0700, Chris Travers wrote: > On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts <jasen@xnet.co.nz> > wrote: > On 2012-09-18, Rafal Pietrak <rafal@zorro.isa-geek.com> wrote: [-------------] > > could be written by user as (3): > > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND > inverse > 20; > > but token/replaced to its form (2) before WHERE evaluation. > > > Macros are confusing: > > select random()*10 as confusion from generate_series(1,10) > where confusion > 5; No dought about that. And as I really cannot tell you if such processing-alias-as-macro (if available) would make me more error prone or not; I deffinitly know, that I often "upsss.." and rewrite an item from SELECT list into the WHERE clause - because as a common sql-user I do forget such nuances. Learning (for good :), that the ".... as <name>" is *not* a "definition of a logical/local short-name for an expression" (e.g. it is, but only within the context of SQL statement evaluation sequence) is really counterintuitive for an sql-user like myself. > > Also you can already do this: > > > CREATE FUNCTION inverse(data) RETURNS NUMERIC LANGUAGE SQL IMMUTABLE > AS $$ > select case when $1.x = 0 then null else 1/$1.x end; > $$; > Hmmm, well. No. This is an overkill to a problem. I'd rather stay with SELECT list item copyed by hand into the WHERE clauses. -R