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

On Mon, 2012-06-18 at 16:21 -0700, Ken LaCrosse wrote:
> 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.

It appears that can work:

------------------------------------------
drop table test1;
drop table a;

create or replace function a_trigger_func() returns trigger
language plpgsql as $$
begin
  perform 1 from pg_tables where tablename='test1';
  if found then
    raise notice 'table already exists';
  else
    raise notice 'table does not exist, creating it...';
    create table test1(i int);
  end if;
  perform 1 from test1;
  return NULL;
end;
$$;

create table a(i int);

create trigger a_trigger after insert on a
  for each row execute procedure a_trigger_func();

foo=# insert into a values (1), (2);
NOTICE:  table does not exist, creating it...
NOTICE:  table already exists
INSERT 0 2
foo=# select * from a;
 i
---
 1
 2
(2 rows)

foo=# select * from test1;
 i
---
(0 rows)
------------------------------------------

Can you post a self-contained case where it doesn't work as you expect?

Regards,
    Jeff Davis

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.