Обсуждение: enforce naming convention
Hi, is it possible to enforce naming convention in pg? Can I create a trigger that runs when a new object is created and see if its name is in an expected format and if not reject it? I had to create a new schema 'zref' storing some of our tables to conceptually separate them but I want to make sure that when we create new tables we can't have the same table name in 'zref' and 'public'. Thanks for the help. Balázs
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Can I create a trigger that runs when a new object is created and see > if its name is in an expected format and if not reject it? Sounds like you need a database-wide trigger on DDL. PostgreSQL has nothing like that at the moment. Your best bet for now is to try and control it at the application level. You could also run an external program (daemon or even a cron job) that continually checks for such a violation and reports it somewhere. > I had to create a new schema 'zref' storing some of our tables to > conceptually separate them but I want to make sure that when we create > new tables we can't have the same table name in 'zref' and 'public'. Allowing two tables with the same name to exist is a prime feature of schemas, so working around that is not generally something that is done. :) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200604150915 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFEQPLxvJuQZxSWSsgRAuP8AJ9awH/+0D8Iy6+hc9qDmbrDDgpWHACgkGFS pQMAGEFJWUbuonyN5+m15wE= =b825 -----END PGP SIGNATURE-----
I know that one of the main reasons why schemas are there is to make it possible to use identical table names, but this time they want to use it to separate tables purely because they feel they would find their ways better that way. On the other hand I feel that this separation will be somewhat arbitrary and may change in the future - to make sure that tables will be freely movable between schemas I want to make sure that there will be no identical names. This may be totally wronb, but what about writing a Before Insert trigger on the pg_catalog.pg_class table checking if there is already a table with that name and if yes prevent the insert? Is there something against doing so? Thanks for the help. SWK
On Mon, Apr 17, 2006 at 07:26:24AM -0700, SunWuKung wrote: > This may be totally wronb, but what about writing a Before Insert > trigger on the pg_catalog.pg_class table checking if there is already a > table with that name and if yes prevent the insert? > Is there something against doing so? Yes, triggers don't work on system catalogs. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.