loading data

Hi All,

I am writing software to load data on a RedHat Enterprise Linux v3 server
running PostgreSQL v7.3.6.

I have all of the external programs worked out and I am now able to load data
into a number of tables in a schema called data_transfer. The next step is to
move the data from the data_transfer schema to its permanent home in various
other schemas. I believe I need a stored procedure for this and I am working
out the basic structure. Here is the pseudo code that I have come up with so
far. Am I headed in the right direction? I would hate to go down the wrong
road too far when one of you could send me off in the correct direction early
on. :-) The other part of this that I need to work out is how to perform the
same process on several dependent tables with a COMMIT/ROLLBACK that affects
everything. Any hints would be appreciated.

Kind Regards,
Keith

FOR rcrd_tbl_example
IN SELECT *
FROM data_transfer.tbl_example
ORDER BY item_id
LOOP
BEGIN;
-- Retrieve a record from the target table to determine UPDATE/INSERT
SELECT count(target_schema.tbl_example.item_id) as v_exist
FROM target_schema.tbl_example
WHERE target_schema.tbl_example.tbl.item_id = rcrd_tbl_example.item_id;
IF v_exist = 1
-- The record exists. Perform an update on the information.
UPDATE target_schema.tbl_example
SET target_schema.tbl_example.col1 = rcrd_tbl_example.col1,
target_schema.tbl_example.col2 = rcrd_tbl_example.col2,
target_schema.tbl_example.col3 = rcrd_tbl_example.col3,
...
target_schema.tbl_example.col4 = rcrd_tbl_example.coln
WHERE target_schema.tbl_example.item_id = rcrd_tbl_example.item_id;
ELSE
-- This is a new record. Perform an insert.
INSERT INTO target_schema.tbl_example
( target_schema.tbl_example.col1,
target_schema.tbl_example.col2,
target_schema.tbl_example.col3,
...
target_schema.tbl_example.col4
)
VALUES ( rcrd_tbl_example.col1,
rcrd_tbl_example.col2,
rcrd_tbl_example.col3,
...
rcrd_tbl_example.coln
)
WHERE target_schema.tbl_example.item_id = rcrd_tbl_example.item_id;
ENDIF
IF no_error
COMMIT;
ELSE
ROLLBACK;
-- Write the offending records into the load error schema
-- if possible for later analysis.
INSERT into load_error.tbl_example
ENDIF
END LOOP;

______________________________________________
99main Internet Services http://www.99main.com