Thread

1. Table containing only valid table names

   Michael Graham <mgraham@bloxx.com> — 2013-04-26T15:09:44Z

   Hi all,
   
   I'm trying to create a table that contains only valid table names.  I'm 
   currently using reglass for the field type which works fine for when you 
   are adding to the table (i.e. you can't add invalid tablesnames to the 
   table).  But it is still possible to leave the table in an invalid state 
   after doing a drop table.
   
   So for example:
   
   => CREATE TABLE table_list (tablename regclass);
   CREATE TABLE
   => INSERT INTO table_list VALUES ('foo');
   ERROR:  relation "foo" does not exist
   LINE 1: INSERT INTO table_list VALUES ('foo');
                                           ^
   => CREATE TABLE foo (a int);
   CREATE TABLE
   => INSERT INTO table_list VALUES ('foo');
   INSERT 0 1
   => DROP TABLE foo;
   DROP TABLE
   => SELECT * FROM table_list;
     tablename
   -----------
     122860
   (1 row)
   
   Does any one have any ideas that could be used to stop this from 
   happening?  I'm not really in the position to have different users for 
   the modification of the table_list and the drops so I don't think I can 
   use different roles.
   
   I'm pretty sure I can't do what I need as postgres doesn't support 
   triggers on DDL but maybe I'm wrong.
   
   Cheers,
   -- 
   Michael Graham
   
   
   
   

2. Re: Table containing only valid table names

   Richard Huxton <dev@archonet.com> — 2013-04-26T15:21:38Z

   On 26/04/13 16:09, Michael Graham wrote:
   > I'm pretty sure I can't do what I need as postgres doesn't support
   > triggers on DDL but maybe I'm wrong.
   
   If you're still in development and not live, it'll be worth checking out 9.3
   
   http://www.postgresql.org/docs/devel/static/event-triggers.html
   
   
   -- 
      Richard Huxton
      Archonet Ltd
   
   
   

3. Re: Table containing only valid table names

   salah jubeh <s_jubeh@yahoo.com> — 2013-04-26T16:08:11Z

   Hello,
   
   You can have the list of table names  from pg_class  such as 
   
   SELECT c.relname as "Name" ,
   FROM pg_catalog.pg_class c
   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE c.relkind IN ('r') AND n.nspname NOT IN  ('pg_toast', 'pg_catalog', 'information_schema') ;
   
   So, if you want to store all table names you can just create a view around the  above query.
   
   Also , the following is not a perfect solution;  but I think it will work.  You can join your table with pg_class to filter out tables which are Dropped 
   
   
   Regards 
   
   
   
   ________________________________
    From: Richard Huxton <dev@archonet.com>
   To: Michael Graham <mgraham@bloxx.com> 
   Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> 
   Sent: Friday, April 26, 2013 5:21 PM
   Subject: Re: [GENERAL] Table containing only valid table names
    
   
   On 26/04/13 16:09, Michael Graham wrote:
   > I'm pretty sure I can't do what I need as postgres doesn't support
   > triggers on DDL but maybe I'm wrong.
   
   If you're still in development and not live, it'll be worth checking out 9.3
   
   http://www.postgresql.org/docs/devel/static/event-triggers.html
   
   
   -- 
      Richard Huxton
      Archonet Ltd
   
   
   

4. Re: Table containing only valid table names

   Jasen Betts <jasen@xnet.co.nz> — 2013-04-28T05:06:19Z

   On 2013-04-26, Michael Graham <mgraham@bloxx.com> wrote:
   > Hi all,
   >
   > I'm trying to create a table that contains only valid table names.  
   
   could you get by with a view off pg_catalog.pg_tables or
   information_schema.tables
   
   -- 
   ⚂⚃ 100% natural