Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

 

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group