Re: Insert Data Into Tables Linked by Foreign Key - Mailing list 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!