Re: DROP IF ...

The following function takes a table name as a parameter and drops
the table and returns true if there are zero rows (otherwise, it
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 '' ||
zerotable;        FETCH zerocurs INTO rowcount;        CLOSE zerocurs;        IF rowcount = 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™

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
> 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