[SQL] DROP IF ...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
CGG
Reply | Threaded
Open this post in threaded view
|

[SQL] DROP IF ...

CGG
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

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

Re: [SQL] DROP IF ...

Thomas F. O'Connell
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings