Thread: Insert Data Into Tables Linked by Foreign Key
Customer ================== Customer_ID | Name Order ============================== Order_ID | Customer_ID | Price To insert an order, here is what I need to do usually, 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? Thanks and Regards Yan Cheng CHEOK
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. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b41d7ac9957514533904!
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
From general point of view, having 3 SQL statement wrapped in a single stored procedure shall perform better due to reducedoverhead to communicate with SQL server. Is that true? Or that is my false assumption? Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/4/10, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > From: Alban Hertroys <dalroi@solfertje.student.utwente.nl> > Subject: Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key > To: "Yan Cheng Cheok" <yccheok@yahoo.com> > Cc: pgsql-general@postgresql.org > Date: Monday, January 4, 2010, 7:57 PM > 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. > > Alban Hertroys > > -- > Screwing up is the best way to attach something to the > ceiling. > > > !DSPAM:737,4b41d7ac9957514533904! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Mon, Jan 04, 2010 at 04:53:16AM -0800, Yan Cheng Cheok wrote: > From general point of view, having 3 SQL statement wrapped in a > single stored procedure shall perform better due to reduced overhead > to communicate with SQL server. Is that true? Or that is my false > assumption? I'd be tempted to put them into a stored procedure/function. Less round trips to server, plan can be cached, and easier code maintenance. You also get the option of tightening up security, functions can be defined as "security definer" and permissions on original tables reduced. -- Sam http://samason.me.uk/
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!
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
insert into foo(a) with recursive t(n) AS (SELECT 10 AS n UNION ALL SELECT n-1 FROM t WHERE n > 0) select n from t; works on 8.5 -- GJ
Can you please provide me an example of a stored procedures to achieve that? Thanks and Regards Yan Cheng CHEOK --- On Mon, 1/4/10, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > From: Alban Hertroys <dalroi@solfertje.student.utwente.nl> > Subject: Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key > To: "Yan Cheng Cheok" <yccheok@yahoo.com> > Cc: pgsql-general@postgresql.org > Date: Monday, January 4, 2010, 7:57 PM > 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. > > Alban Hertroys > > -- > Screwing up is the best way to attach something to the > ceiling. > > > !DSPAM:737,4b41d7ac9957514533904! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On 5 Jan 2010, at 4:26, Yan Cheng Cheok wrote:
> Can you please provide me an example of a stored procedures to achieve that?
>
> Thanks and Regards
> Yan Cheng CHEOK
Sure. The one below should even protect you against concurrent inserts. I didn't test it though, there may be some
typosetc.
CREATE OR REPLACE FUNCTION insert_order(_customer_name, _price)
RETURNS integer
LANGUAGE 'plpgsql' STABLE
AS $body$
DECLARE
_customer_id int;
BEGIN
LOOP;
SELECT INTO _customer_id Customer_ID FROM Customer WHERE name = _customer_name;
EXIT WHEN FOUND;
BEGIN;
INSERT INTO Customer (name)
VALUES (_customer_name)
RETURNING Customer_ID INTO _customer_id;
EXIT;
EXCEPTION WHEN unique_violation THEN
-- Do nothing
END;
END LOOP;
INSERT INTO Order (Customer_ID, Price) VALUES (_customer_id, _price);
END;
$body$;
> --- On Mon, 1/4/10, Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:
>
>> From: Alban Hertroys <dalroi@solfertje.student.utwente.nl>
>> Subject: Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key
>> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
>> Cc: pgsql-general@postgresql.org
>> Date: Monday, January 4, 2010, 7:57 PM
>> 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.
>>
>> Alban Hertroys
>>
>> --
>> Screwing up is the best way to attach something to the
>> ceiling.
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
Alban Hertroys
--
Screwing up is the best way to attach something to the ceiling.
!DSPAM:737,4b43203010731568117995!