In-Reply-To: <20050524174454.67174.qmail@web32501.mail.mud.yahoo.com>
References: <20050524174454.67174.qmail@web32501.mail.mud.yahoo.com>
Mime-Version: 1.0 (Apple Message framework v730)
Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed
Message-Id: <661E48E2-623E-494E-8782-083B31456F5A@sitening.com>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: "Thomas F. O'Connell" <tfo@sitening.com>
Subject: Re: DROP IF ...
Date: Tue, 24 May 2005 17:07:19 -0500
To: cgg007@yahoo.com

The following function takes a table name as a parameter and drops =20
the table and returns true if there are zero rows (otherwise, it =20
returns false):

CREATE OR REPLACE FUNCTION dropzero( varchar ) RETURNS bool AS '
DECLARE
         zerotable ALIAS FOR $1;
         zerocurs refcursor;
         rowcount int;
BEGIN
         OPEN zerocurs FOR EXECUTE ''SELECT COUNT( * ) FROM '' || =20
zerotable;
         FETCH zerocurs INTO rowcount;
         CLOSE zerocurs;
         IF rowcount =3D 0 THEN
                 EXECUTE ''DROP TABLE '' || zerotable;
                 RETURN true;
         ELSE
                 RETURN false;
         END IF;
END;
' LANGUAGE 'plpgsql';

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i=99

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On May 24, 2005, at 12:44 PM, CG wrote:

> PostgreSQL 7.4 ...
>
> I'm trying to find a way to drop a table via SQL if it contains 0 =20
> 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) =3D 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 =20
> going to be
> dropped. I need a different strategy.
>
> Please advise!
>
> CG