Postgres Pro
Downloads
Home   >   mailing lists

Thread: inserting references

inserting references

From
ktt
Date:
I have a table, which references
values in other 3 tables.

I would like to insert values in
bananas, oranges and kivi tables
and insert their bananas_id,oranges_id,kivi_id,
country
in fruits table.

What are better methods for doing
this(exept LAST_INSERT_ID())?
Would be nice to use the most compact form.



sample tables scheme

-----------   ----------   ----------   ----------

fruits        bananas      oranges      kivi

-----------   ----------   ----------   ----------

fruits_id     bananas_id   oranges_id   kivi_id

bananas_id    date_bn      date_or      date_kv

oranges_id    ----------   ----------   ----------

kivi_id
country

-----------

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

Re: inserting references

From
Martijn van Oosterhout
Date:
On Fri, Jun 14, 2002 at 01:14:08AM -0700, ktt wrote:
> I have a table, which references
> values in other 3 tables.
>
> I would like to insert values in
> bananas, oranges and kivi tables
> and insert their bananas_id,oranges_id,kivi_id,
> country
> in fruits table.
>
> What are better methods for doing
> this(exept LAST_INSERT_ID())?
> Would be nice to use the most compact form.

If you're using sequences, just use currval() in the insert statement for
fruits.

> sample tables scheme
>
> -----------   ----------   ----------   ----------
>
> fruits        bananas      oranges      kivi
>
> -----------   ----------   ----------   ----------
>
> fruits_id     bananas_id   oranges_id   kivi_id
>
> bananas_id    date_bn      date_or      date_kv
>
> oranges_id    ----------   ----------   ----------
>
> kivi_id
> country
>
> -----------
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Re: inserting references

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Jun 14, 2002 at 01:14:08AM -0700, ktt wrote:
>> I have a table, which references
>> values in other 3 tables.
>>
>> I would like to insert values in
>> bananas, oranges and kivi tables
>> and insert their bananas_id,oranges_id,kivi_id,
>> country
>> in fruits table.

> If you're using sequences, just use currval() in the insert statement for
> fruits.

If this is always supposed to happen, why have the client issue insert
commands against fruits at all?  Set up ON INSERT triggers on the detail
tables that issue inserts into the summary table.  They can get all the
info they need from the "new" record for the detail table.

            regards, tom lane