On Mon, Feb 1, 2010 at 10:38 PM, Yan Cheng Cheok
<yccheok@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;