Thread: 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