COPY, Triggers and visibility into pg_tables

COPY fp_eitem_price_parent (sku, store_number, effective_date, reg_for_qty, reg_price) 

FROM 'C:\Projects\Raleys\postgres\partitions\upload\ec_weekly.113.upl' (FORMAT text, DELIMITER '|', NULL '')

which causes a CREATE OR REPLACE trigger to fire.

This trigger will create a DB table based on a couple of parameters in the line being copied into the database.  The way it's determining whether to create the table is to do a query against the pg_tables view looking for the table name.  I had assumed that once the CREATE TABLE had executed that the next time I queried the pg_tables view that I would find an entry for the new table.  Turns out that's not the case.

I'm assuming (dangerous I know) that the query is not finding the new pg_tables entry because the COPY command is operating under a transaction and no database changes will be seen until it completes.  In any event the next row in the file that's being copied into the database also tries to create the new table which then causes the pgplsql code to abort.  

My question is this:

If I can't query the pg_tables view to determine that I've already created the table how should I determine if the table already exists?  The trigger is being called for each row being copied and I don't see any obvious ways to maintain state between trigger calls so that the first trigger execution could inform the inform subsequent executions that the table has already been created.  Any ideas? 

Thanks in advance.

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

Ken LaCrosse | Enterprise Architect

500 West Capitol Avenue

West Sacramento, California 95605-2696
   P: 916.373.6571 | F: 916.373.6553
   E: klacross@raleys.com

IT'S A MOM'S WORLD

We're just here to help.™ Visit www.raleys.com for blogs, recipes and savings.

Warning:  this e-mail may contain information proprietary to Raley's
and is intended only for the use of the intended recipient(s).  
If the read of this message is not an intended recipient,
you are hereby notified that you have received this message in
error and that any review, dissemination, distribution, or copying
of this message is strictly prohibited.  If you have received this message
in error, please notify the sender immediately.