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