Re: insert aliases?

** Reply to message from Tom Lane  on Tue, 18 Mar 2003
09:37:33 -0500
Hi Tom
 Fair comment, but it doesn't make porting any easier :) 
 Here is an (admittedly bad) example of 1 of a couple hundred
update/delete/insert statements I will have to hand modify to get this app to
run over postgress. If you think it's ugly now (and it is), wait till I remove
the table alias :).

My point is again, lack of this feature(along with no with hold cursors, and
lower case table and column name folding) is a real barrier to porting an app
to run over postgress.

           EXEC SQL
           UPDATE BACCHUS.REBUILD_LIN T1 
           SET T1.REBUILD_LIN_NOTES = 
           CASE WHEN T1.SERIAL_NUMBER IS NULL 
           THEN 'From plant: ' || COALESCE((SELECT T2.PLANT_CODE 
           FROM BACCHUS.PLANT T2 
           WHERE T2.COMPANY_ID = T1.COMPANY_ID 
           AND T2.PLANT_ID=T1.PLANT_ID) , ' ') 
           || ' on work order: ' || 
           COALESCE((SELECT CAST(T2.SHEET_NUMBER AS VARCHAR) 
           FROM BACCHUS.TBTRAN_HDR T2 
           WHERE T2.COMPANY_ID = T1.COMPANY_ID 
           AND T2.TBTRAN_HDR_ID=T1.SOURCE_HDR_ID),' ') 
           ELSE 'From plant: ' || 
           COALESCE((SELECT T2.PLANT_CODE 
           FROM BACCHUS.PLANT T2 
           WHERE T2.COMPANY_ID = T1.COMPANY_ID 
           AND T2.PLANT_ID=T1.PLANT_ID) ,' ') || ' on work order: ' 
           || COALESCE((SELECT CAST(T2.SHEET_NUMBER AS VARCHAR) 
           FROM BACCHUS.TBTRAN_HDR T2 
           WHERE T2.COMPANY_ID = T1.COMPANY_ID 
           AND T2.TBTRAN_HDR_ID=T1.SOURCE_HDR_ID),' ') 
           || ' from position: ' 
           || COALESCE((SELECT CAST(T2.POSITION_CODE AS VARCHAR) 
           FROM BACCHUS.TBTRAN_LIN T2 
           WHERE T2.COMPANY_ID = T1.COMPANY_ID 
           AND T2.TBTRAN_HDR_ID=T1.SOURCE_HDR_ID 
           AND T2.LINE_NUMBER=T1.SOURCE_LINE_NUMBER),' ') 
           || ' of condition: ' || T1.CONDITION_CODE 
           || ' at Odometer: ' || 
           COALESCE((SELECT CAST(T2.ODOMETER AS VARCHAR) 
           FROM BACCHUS.TBTRAN_LIN T2 
           WHERE T2.COMPANY_ID = T1.COMPANY_ID 
           AND T2.TBTRAN_HDR_ID=T1.SOURCE_HDR_ID 
           AND T2.LINE_NUMBER=T1.SOURCE_LINE_NUMBER),' ') END 
           WHERE T1.REBUILD_HDR_ID = :RBL-REBUILD-HDR-ID
             AND T1.LINE_NUMBER = :RBL-LINE-NUMBER
           END-EXEC.

Regards,
Wayne

> "Wayne Armstrong"  writes:
> > It seems that postgress doesn't accept aliases for the tablename in updates,
> > inserts, and deletes as it does do for selects ????
> 
> Nope, and neither does the SQL standard ...
> 
> 			regards, tom lane