On Mon, Feb 1, 2010 at 10:38 PM, Yan Cheng Cheok <ycch...@yahoo.com> wrote:
> May I know how I can use trigger technique, to remove the table itself, > when after delete operation, there is 0 row in the table? > > For the record, I think having a trigger drop a table automatically when it's empty is probably a bad idea. But I tried it out anyways, and got a surprising: ERROR: relation 16400 is still open when the trigger function attempted to drop the table. I searched a bit in an attempt to learn if this error message is bogus or not for this case, and found a few threads such as: http://archives.postgresql.org/pgsql-novice/2007-02/msg00099.php which suggest that having a cursor open on the table, or a PL/pgSQL FOR-IN-SELECT loop open would be a legitimate(?) cause for the error. In this case, I'm just using a: SELECT COUNT(*) INTO num_rows ... statement. Can anyone say whether the error message I'm seeing is valid? I've tested on CVS head and 8.3.4 and got the same error. Josh -- -- Test case below: BEGIN; CREATE TABLE mytable (name text PRIMARY KEY); CREATE OR REPLACE FUNCTION "drop_mytable_ifempty"() RETURNS trigger AS $$ DECLARE num_rows int; BEGIN SELECT COUNT(*) INTO num_rows FROM "mytable"; IF num_rows = 0 THEN RAISE NOTICE 'Dropping mytable!'; DROP TABLE "mytable"; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; CREATE TRIGGER "drop_mytable_ifempty_trg" AFTER DELETE ON "mytable" FOR EACH ROW EXECUTE PROCEDURE "drop_mytable_ifempty"(); INSERT INTO "mytable" ("name" ) VALUES ('joe2'); INSERT INTO "mytable" ("name" ) VALUES ('joe'); DELETE FROM "mytable" WHERE name = 'joe'; DELETE FROM "mytable" WHERE name = 'joe2'; ROLLBACK;
