Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group