Re: Insert Data Into Tables Linked by Foreign Key

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