Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Transfer-Encoding: 7bit
Message-Id: <E3D00FA5-C52E-4886-AB45-994162498DA8@joelrichard.com>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
To: pgsql-sql@postgresql.org
From: Joel Richard <postgres@joelrichard.com>
Subject: Converting from MS Access field aliases 
Date: Thu, 12 Jul 2007 10:07:41 -0400

Good morning,

Oh joyous day! We are upgrading a legacy database system from MS  
Access to PostgreSQL! Yay!

Ok, rejoicing over. Here's our issue and PLEASE point me to the right  
place if this has been discussed before.

In MS Access one can reuse field aliases later in the same query. For  
example:

   SELECT field1 / 2 AS foo,
          field2 * 2 AS bar,
          foo + bar AS total
   WHERE foo < 12;

The first two fields are fine, it's the third that's a problem. The  
database reports

   ERROR:  column "foo" does not exist

This type of situation is happening -many- times in well over fifty  
existing SELECT..INTO and INSERT INTO queries. The obvious solution  
here is to copy the code around to eliminate the need to reuse "foo"  
and "bar" in the query:

   SELECT field1 / 2 AS foo,
          field2 * 2 AS bar,
          (field1 / 2) + (field2 * 2) AS total
   WHERE (field1 / 2) < 12;

But this is a bit ugly and cumbersome and in our case, not desirable  
since foo and bar get used many times in the remains of the query. To  
replace them with the formulae means that debugging is quite  
difficult and very prone to errors.

Does anyone have suggestions on how to circumvent this in a more  
graceful manner? I mean I could probably find a way to do this with a  
couple of queries and some views, or maybe write a function (or more  
like 30 functions) to do the work, but both of those only add to the  
workload in an undesirable manner. :)

To complicate matters, performance is a concern. We're operating on  
upwards of a billion records. Not all at the same time, but the goal  
is to run these  a series of calculations will be done on all of the  
data.

Thanks for any input that you might have.

--Joel