--On Mittwoch, Juni 08, 2005 14:48:55 -0400 Alvaro Herrera
<[EMAIL PROTECTED]> wrote:
On Wed, Jun 08, 2005 at 08:25:12PM +0200, Bernd Helmle wrote:
One issue that comes to my mind is what to do when dealing with tables
that have assigned triggers and sequences (serials). Do we want to move
them as well or leave them in the source namespace?
I'd think it's important that the ALTER TABLE leaves things just like
what you'd end up with if you created the table in the new schema in the
first place. i.e., indexes, triggers, sequences should be moved too.
That leads me to the question what gets attached to a table:
SEQUENCE, INDEX, TRIGGER (function), CONSTRAINT, .... ?
One issue to check is what happens if you move the table and trigger but
the function remains in the original namespace. Is this a problem if
the new namespace is not in the search path?
Hmm have triggers an own namespace? I can see in pg_trigger that they are
attached to pg_proc, but can't see an own namespace specification...
However, lets have a look at this example:
[EMAIL PROTECTED]:bernd #= CREATE SCHEMA B;
CREATE SCHEMA
[EMAIL PROTECTED]:bernd #= set search_path TO b;
SET
[EMAIL PROTECTED]:bernd #= CREATE TABLE test ( id integer not null primary key
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE
[EMAIL PROTECTED]:bernd #= CREATE TABLE log_test ( usr text, log_time timestamp
default NOW() );
CREATE TABLE ^
[EMAIL PROTECTED]:bernd #= CREATE OR REPLACE FUNCTION trigger_log_update()
RETURNS TRIGGER AS $$ BEGIN INSERT INTO log_test VALUES( current_user );
RETURN new; END; $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
[EMAIL PROTECTED]:bernd #= CREATE TRIGGER t_log_update AFTER UPDATE OR DELETE
OR INSERT ON test FOR STATEMENT EXECUTE PROCEDURE trigger_log_update();
CREATE TRIGGER
[EMAIL PROTECTED]:bernd #= INSERT INTO test VALUES (2);
INSERT 0 1
[EMAIL PROTECTED]:bernd #= CREATE SCHEMA C;
CREATE SCHEMA
[EMAIL PROTECTED]:bernd #= ALTER TABLE test SET SCHEMA C;
NOTICE: changed dependency to new schema "c"
ALTER TABLE
[EMAIL PROTECTED]:bernd #= SET search_path TO C;
SET
[EMAIL PROTECTED]:bernd #= INSERT INTO test VALUES (4);
INSERT 0 1
So that works, but let's move the trigger function as well:
[EMAIL PROTECTED]:bernd #= ALTER FUNCTION B.trigger_log_update() SET SCHEMA C;
NOTICE: changed dependency to new schema "c"
ALTER TABLE
[EMAIL PROTECTED]:bernd #= INSERT INTO test VALUES (5);
ERROR: relation "log_test" does not exist
CONTEXT: SQL statement "INSERT INTO log_test VALUES( current_user )"
PL/pgSQL function "trigger_log_update" line 1 at SQL statement
So that doesn't work and it's likely that someone can mess up his schema
with this, because the trigger function no longer finds its "log table".
Don't know how to deal with that.....
--
Bernd
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
