X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1C14252825 for ; Tue, 24 May 2005 14:45:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81638-05 for ; Tue, 24 May 2005 17:44:55 +0000 (GMT) Received: from web32501.mail.mud.yahoo.com (web32501.mail.mud.yahoo.com [68.142.207.211]) by svr1.postgresql.org (Postfix) with SMTP id 6684152888 for ; Tue, 24 May 2005 14:44:54 -0300 (ADT) Received: (qmail 67176 invoked by uid 60001); 24 May 2005 17:44:54 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=seFb7DBYhB9ga5kd60tjBi/GfyljmqsJZGBov3sVXekypRlUixkfKCbMLHY+pWWvv2BcfcuXoeGJVwp0cKG8E/tPu0vWDidKceFFE/YDzOSszcu537/IvEYKPhC6BbE8fXdrN7tiQiHKb4qjzt+0ZXCQALrL0Y2OHmR1KIX5GB8= ; Message-ID: <20050524174454.67174.qmail@web32501.mail.mud.yahoo.com> Received: from [216.173.173.66] by web32501.mail.mud.yahoo.com via HTTP; Tue, 24 May 2005 10:44:54 PDT Date: Tue, 24 May 2005 10:44:54 -0700 (PDT) From: CG Reply-To: cgg007@yahoo.com Subject: DROP IF ... To: pgsql-sql@postgresql.org In-Reply-To: 6667 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.857 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_YAHOO_RCVD, FROM_ENDS_IN_NUMS, SUBJ_ALL_CAPS, UPPERCASE_25_50 X-Spam-Level: ** X-Archive-Number: 200505/204 X-Sequence-Number: 21517 PostgreSQL 7.4 ... I'm trying to find a way to drop a table via SQL if it contains 0 rows. Here was my thought: CREATE OR REPLACE FUNCTION dropif(text, bool) RETURNS bool AS 'DECLARE tblname ALIAS FOR $1; condition ALIAS FOR $2; BEGIN IF (condition) THEN EXECUTE(\'DROP TABLE "\' || tblname || \'";\'); END IF; RETURN \'t\'::bool; END;' LANGUAGE 'plpgsql' VOLATILE; ... then ... BEGIN; CREATE TABLE testtbl (i int4); SELECT dropif('testtbl',(SELECT count(*)::int4 FROM testtbl) = 0); ERROR: relation 286000108 is still open CONTEXT: PL/pgSQL function "dropif" line 6 at execute statement ... It makes sense. The select is still open when the table is going to be dropped. I need a different strategy. Please advise! CG __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com