Re: Insert Data Into Tables Linked by Foreign Key

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?

Thanks and Regards
Yan Cheng CHEOK

--- On Mon, 1/4/10, Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:

> From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
> Subject: Re: [GENERAL] Insert Data Into Tables Linked by Foreign Key
> To: "Yan Cheng Cheok" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>