Обсуждение: escaping function
I understand there is an escaping function for queries in postgres. 1/ Is it in pl/pgsql, and callable from standard queries? 2/ does it handle: A/ 'chr(' B/ ''' C/ '"' D/ '--' E/ ';'
Hi, I get an error on delete, insert and update statements of the form :- insert into fault_log t1 select.... update fault_log t1 set ... delete from fault_log t1 where .... etc. It seems that postgress doesn't accept aliases for the tablename in updates, inserts, and deletes as it does do for selects ???? All the above work with just insert into fault_log select ... etc, but not having the shorthand alias for the table name to use in a subselect is a royal pain (porting from a database that does allow aliases here) Oh well - lots of rewriting of insert, update and delete statements to do - icky, icky icky :) 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
> "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 ... > But it would be VERY useful for the UPDATE command... Postgres has that nice extension of multi-table where clause for updates, but it can be confusing when you want to specify the updated table for a self-join. Being able to alias the updated table too would make things cleaner. Cheers, Csaba.
** 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
"Wayne Armstrong" <wdarmst@bacchus.com.au> writes: > Fair comment, but it doesn't make porting any easier :) I would not have any objection to a patch to add aliases to UPDATE/DELETE (but I'm not sure what an alias on INSERT would refer to). Someone's got to step up and do the work though... regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I would not have any objection to a patch to add aliases to UPDATE/DELETE > (but I'm not sure what an alias on INSERT would refer to). Someone's > got to step up and do the work though... Perhaps this should be added to the TODO page? - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200303190938 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+eIEbvJuQZxSWSsgRAlvXAJoCnyNlSgU3IGVz2atainK9ttYUJgCggpt1 jnnRag8zr0MvMOwH7bncGVg= =Feq7 -----END PGP SIGNATURE-----