Re: COPY, Triggers and visibility into pg_tables

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Ken LaCrosse <klacross(at)raleys(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: COPY, Triggers and visibility into pg_tables
Date: 2012-06-22 19:58:25
Message-ID: 1340395105.16713.33.camel@sussancws0025
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bedorf, Paul 2012-06-26 19:22:20 How to Add User with Select Priviliges only
Previous Message JOSE MORAN 2012-06-20 12:14:05 Re: Unknown winsock error 10061