inserts within function, within insert

From: "Kevin Duffy" <KD(at)wrinvestments(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: inserts within function, within insert
Date: 2008-11-10 17:11:01
Message-ID: DFC309C8A42633419600522FA8C4AE1A5610B7@mail-01.wrcapital.corp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-sql

Hello All:

I am hoping to get some guidance from the local gurus, before I go off
and

write this code.

Consider this scenario:

I receive a file containing inventory info, make it simple, clothes
items.

Within the file there is info for different locations, so an inventory
item

can occur more than once. I.e. a certain jacket could be in the

inventory at more than one location. It is also possible

that an inventory item in the file could be a new item,

an item that is not in my item table.

When I parse and import the table there will be enough info provided,

so that I can create the new inventory items.

I would like to write a function that based upon the info provided for
an inventory item will

either return the item's key or if the item is new, create the item and
then return the item's key.

Say I am doing a simple insert to inventory like the following:

INSERT INTO INVENTORY ( itemkey, locationkey, qty)

SELECT getitemkey(itemtype, style, color, size), lockey, qty
from IMPORT_INV

Question:

New records added to the ITEM table within getitemkey(), will they
be available to inserts that follow?

I.e. if record 2 in IMPORT_INV is for the same clothing item as
record 27, and this a new inventory item,

but for different locations. Will this new inventory item get
added twice to my ITEM table.

Many thanks for considering this issue.

Kevin Duffy

Browse pgsql-sql by date

  From Date Subject
Next Message Johnson, Michael L. 2008-11-10 18:06:42 Re: Subsorting GROUP BY data
Previous Message Tom Lane 2008-11-10 16:57:55 Re: Using UTF strings in pg8.3 - storing hexadecimal values in bytea columns