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