Re: insert aliases?

** Reply to message from Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)bacchus(dot)com(dot)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