Re: COPY, Triggers and visibility into pg_tables

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