In response to Alban Hertroys : > On 4 Jan 2010, at 13:15, A. Kretschmer wrote: > > > 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 > > > That looked interesting enough that I gave it a try, even though AFAIK we don't have writable CTE's yet (8.5 maybe?). Maybe, i hope ;-) Btw.: http://akretschmer.blogspot.com/2009/11/writeable-cte-short-performance-test.html > Below is my first attempt at any CTE at all. Is there anything that can be improved here? It doesn't look all that optimal... > > WITH t1 AS ( > SELECT * FROM Customer WHERE name = 'John' > UNION > INSERT INTO Customer (name) > SELECT 'John' FROM generate_series(1,1) AS C1 > WHERE NOT EXISTS ( > SELECT 1 FROM Customer AS C2 WHERE name = 'John' > ) > RETURNING * > ) > INSERT INTO Order (t1.id, 1.34); I'm not an expert, sorry, and i haven't that available atm, but i think it's okay. (except that the table contains 3 columns ...) 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
