Re: Insert Data Into Tables Linked by Foreign Key

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?).
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);

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b41e73f9952042619622!