Re: COPY, Triggers and visibility into pg_tables

The problem was in my use of a quote_literal instead of a quote_ident when
checking for the existence of the table.

Thanks for the example it helped me figure out what was wrong.

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

Ken LaCrosse | Enterprise Architect
500 West Capitol Avenue
West Sacramento, California 95605-2696
P: 916.373.6571 | F: 916.373.6553
E: klacross <klacross(at)raleys(dot)com>@raleys.com <rjacobsen(at)raleys(dot)com>

*IT'S A MOM'S WORLD*
We're just here to help.™ Visit www.raleys.com for blogs, recipes and
savings.

On Fri, Jun 22, 2012 at 12:58 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

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