In response to Alban Hertroys : > On 4 Jan 2010, at 9:53, Yan Cheng Cheok wrote: > > > For example, "John" place "1.34" priced order. > > > > (1) Get Customer_ID from Customer table, where name is "John" > > (2) If there are no Customer_ID returned (There is no John), insert "John" > > (3) Get Customer_ID from Customer table, where name is "John" > > (4) Insert "Customer_ID" and "1.34" into Order table. > > > > There are 4 SQL communication with database involved for this simple operation!!! > > > > Is there any better way, which can be achievable using 1 SQL statement? > > > You don't need the 3rd statement if you use INSERT .. RETURNING at step 2. > > The one way you could achieve this by calling only one statement that > I can think of is to wrap this in a stored procedure. Plain SQL > doesn't provide any means to do what you want. Writeable CTE can do that ;-) http://wiki.postgresql.org/images/c/c0/PGDay2009-EN-Writeable_CTEs_The_Next_Big_Thing.pdf Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
