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
