From: Thomas F. O'Connell <tfo@sitening.com>
To: cgg007@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: DROP IF ...
Date: Tue, 24 May 2005 17:07:19 -0500
Message-ID: <661E48E2-623E-494E-8782-083B31456F5A@sitening.com> (raw)
In-Reply-To: <20050524174454.67174.qmail@web32501.mail.mud.yahoo.com>
References: <20050524174454.67174.qmail@web32501.mail.mud.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@sitening.com, cgg007@yahoo.com
Subject: Re: DROP IF ...
In-Reply-To: <661E48E2-623E-494E-8782-083B31456F5A@sitening.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox