Re: insert aliases? - Mailing list pgsql-general

** Reply to message from Tom Lane <tgl@sss.pgh.pa.us> 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" <wdarmst@bacchus.com.au> 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