Postgres Pro
Downloads
Home   >   mailing lists

Thread: escaping function

escaping function

From
Dennis Gearon
Date:
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/ ';'

insert aliases?

From
"Wayne Armstrong"
Date:
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

Re: insert aliases?

From
Tom Lane
Date:
"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

Re: insert aliases?

From
Csaba Nagy
Date:
> "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.

Re: insert aliases?

From
"Wayne Armstrong"
Date:
** 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

Re: insert aliases?

From
Tom Lane
Date:
"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

Re: insert aliases?

From
greg@turnstep.com
Date:
-----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-----