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 typos etc.
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@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
>> From: Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>
>> Subject: Re: Insert Data Into Tables Linked by Foreign Key
>> To: "Yan Cheng Cheok" <yccheok@xxxxxxxxx>
>> Cc: pgsql-general@xxxxxxxxxxxxxx
>> 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@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> 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!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
