Problem with aliasing
I wrote a plpgsql function to return the maximum of three dates.
TAL=# select max(req_del_date1, req_del_date2, req_del_date3) as ma1
from invoices;
ma1
------------
2003-02-25
2003-02-25
(2 rows)
However I'm having problems with the following query:
TAL=# select max(req_del_date1, req_del_date2, req_del_date3) as max
from invoices where max <= now();
ERROR: Attribute "max" not found
Why can't the where part of the query see "max"? I've tried aliasing the
returned value but that did not work either:
TAL=# select max(req_del_date1, req_del_date2, req_del_date3) as ma1
from invoices where ma1 <= now();
ERROR: Attribute "ma1" not found
What is wrong with my syntax?
Thanks!
Jc
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
TAL=# select max(req_del_date1, req_del_date2, req_del_date3) as ma1 from
invoices where ma1 <= now();
ERROR: Attribute "ma1" not foundWhat is wrong with my syntax?
Try either of:
SELECT max(req1, req2, req3) AS max FROM invoices WHERE max(req1, req2, req3) <= now()
SELECT * from (select max(req1, req2, req3) AS max FROM invoices) WHERE max <= now()
Whichever seems clearer to you, I think postgres actually runs the two the
same way. Note, you should mark your function immutable so postgres knows it
can optimize the second case into the first.
--
greg
Greg Stark wrote:
Try either of:
SELECT max(req1, req2, req3) AS max FROM invoices WHERE max(req1, req2, req3) <= now()
Ok, that works. But why can't I alias the result of the max() function
and use the alias in the where clause? Something like:
SELECT max(r1,r2,r3) as max from invoices WHERE max <= now();
Why can't postgres see the alias when inside the WHERE clause?
Jc
On 21 Feb 2003, Greg Stark wrote:
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
TAL=# select max(req_del_date1, req_del_date2, req_del_date3) as ma1 from
invoices where ma1 <= now();
ERROR: Attribute "ma1" not foundWhat is wrong with my syntax?
Try either of:
SELECT max(req1, req2, req3) AS max FROM invoices WHERE max(req1, req2, req3) <= now()
SELECT * from (select max(req1, req2, req3) AS max FROM invoices) WHERE max <= now()Whichever seems clearer to you, I think postgres actually runs the two the
same way. Note, you should mark your function immutable so postgres knows it
can optimize the second case into the first.
Doesn't:
SELECT max(req1, req2, req3) AS ma1 FROM invoices HAVING ma1 <= now();
work?
Now I'm going to have to go see if I've got that wrong as well...
--
Nigel J. Andrews
On Fri, 21 Feb 2003, Nigel J. Andrews wrote:
On 21 Feb 2003, Greg Stark wrote:
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
TAL=# select max(req_del_date1, req_del_date2, req_del_date3) as ma1 from
invoices where ma1 <= now();
ERROR: Attribute "ma1" not foundWhat is wrong with my syntax?
Try either of:
SELECT max(req1, req2, req3) AS max FROM invoices WHERE max(req1, req2, req3) <= now()
SELECT * from (select max(req1, req2, req3) AS max FROM invoices) WHERE max <= now()Whichever seems clearer to you, I think postgres actually runs the two the
same way. Note, you should mark your function immutable so postgres knows it
can optimize the second case into the first.Doesn't:
SELECT max(req1, req2, req3) AS ma1 FROM invoices HAVING ma1 <= now();
work?
Now I'm going to have to go see if I've got that wrong as well...
Obviously I got the wrong end of the stick but any way, I was wrong that column
aliases could be used for group by and having clauses. At least that's what
7.2.3 is telling me.
--
Nigel J. Andrews