Re: if exists? - Mailing list pgsql-general

----- Original Message -----
From: "Vincent Stoessel" <vincent@xaymaca.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, May 13, 2002 3:33 PM
Subject: [GENERAL] if exists?


> Is there an sql query that I can use on postgres that will tell
> me if a table exists? I'm writing a perl script thatr creates a table.
> But I want it to rename a table with the same name if it finds an
> existing one.
> Thanks
> --
> Vincent Stoessel
> Linux Systems Developer
> vincent xaymaca.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Try with functions I wrote in pl-psql:


----------------------------------------------------------------------------
----------
/*Execute first expression if user exist, second expression if user does not
exist
 How to use :
  SELECT iif_exist_user('finteh','DROP USER finteh','--Do nothing')
*/
CREATE OR REPLACE FUNCTION iif_exist_user(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cUserName  ALIAS FOR $1;
  cSQLTrue  ALIAS FOR $2;
  cSQLFalse ALIAS FOR $3;
  nCnt int4;
BEGIN
 SELECT COUNT(*) FROM pg_user WHERE usename=cUserName::name INTO nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_user(varchar,varchar,varchar) IS 'Execute
first expression if user exist, second expression if user does not exist';
----------------------------------------------------------------------------
----------

----------------------------------------------------------------------------
----------
/*Execute first expression if group exist, second expression if group does
not exist
 How to use :
  SELECT iif_exist_group('App_Admin','--Do Nothing','CREATE GROUP
"App_Admin" WITH sysid 1001')
*/
CREATE OR REPLACE FUNCTION iif_exist_group(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cGroupName  ALIAS FOR $1;
  cSQLTrue  ALIAS FOR $2;
  cSQLFalse ALIAS FOR $3;
  nCnt int4;
BEGIN
 SELECT COUNT(*) FROM pg_group WHERE groname=cGroupName::name INTO nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_group(varchar,varchar,varchar) IS 'Execute
first expression if group exist, second expression if group does not exist';
----------------------------------------------------------------------------
----------

----------------------------------------------------------------------------
----------
/*Execute first expression if table exist, second expression if table does
not exist
 How to use :
  SELECT if_exist_table_exec('server_list','DROP TABLE server_list','--Do
nothing')
*/
CREATE OR REPLACE FUNCTION iif_exist_table(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cTableName  ALIAS FOR $1;
  cSQLTrue  ALIAS FOR $2;
  cSQLFalse ALIAS FOR $3;
  nCnt int4;
BEGIN
    SELECT COUNT(*) FROM pg_tables WHERE tablename=cTableName::name INTO
nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_table(varchar,varchar,varchar) IS 'Execute
first expression if table exist, second expression if table does not exist';
----------------------------------------------------------------------------
----------

----------------------------------------------------------------------------
----------
/*Execute first expression if view exist, second expression if view does not
exist
 How to use :
  SELECT iif_exist_view('server_list','DROP VIEW server_list','--Do
nothing')
*/
CREATE OR REPLACE FUNCTION iif_exist_view(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cViewName  ALIAS FOR $1;
  cSQLTrue  ALIAS FOR $2;
  cSQLFalse ALIAS FOR $3;
  nCnt int4;
BEGIN
    SELECT COUNT(*) FROM pg_views WHERE viewname=cViewName::name INTO nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_view(varchar,varchar,varchar) IS 'Execute
first expression if view exist, second expression if view does not exist';
----------------------------------------------------------------------------
----------

----------------------------------------------------------------------------
----------
/*Execute first expression if operator exist, second expression if operator
does not exist
 How to use :
  SELECT iif_exist_operator('|<','','Raise error - Operator does not exist')
*/
CREATE OR REPLACE FUNCTION iif_exist_operator(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cOperatorName  ALIAS FOR $1;
  cSQLTrue   ALIAS FOR $2;
  cSQLFalse  ALIAS FOR $3;
  nCnt int4;
BEGIN
    SELECT COUNT(*) FROM pg_operator WHERE oprname=cOperatorName::name INTO
nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_operator(varchar,varchar,varchar) IS 'Execute
first expression if operator exist, second expression if operator does not
exist';
----------------------------------------------------------------------------
----------


----------------------------------------------------------------------------
----------
/*Execute first expression if rule exist, second expression if rule does not
exist
 How to use :
  SELECT iif_exist_rule('zemlje_rule','DROP RULE zemlje_rule','--Do
nothing')
*/
CREATE OR REPLACE FUNCTION iif_exist_rule(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cRuleName   ALIAS FOR $1;
  cSQLTrue   ALIAS FOR $2;
  cSQLFalse  ALIAS FOR $3;
  nCnt int4;
BEGIN
    SELECT COUNT(*) FROM pg_rules WHERE rulename=cRuleName::name INTO nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_rule(varchar,varchar,varchar) IS 'Execute
first expression if rule exist, second expression if rule does not exist';
----------------------------------------------------------------------------
----------

----------------------------------------------------------------------------
----------
/*Execute first expression if sequence exist, second expression if sequence
does not exist
 How to use :
  SELECT iif_exist_sequence('zemlje_id_seq','DROP SEQUENCE
zemlje_id_seq','--Do nothing')
*/
CREATE OR REPLACE FUNCTION iif_exist_sequence(varchar,varchar,varchar)
    RETURNS boolean
AS '
DECLARE cSequenceName ALIAS FOR $1;
  cSQLTrue   ALIAS FOR $2;
  cSQLFalse  ALIAS FOR $3;
  nCnt int4;
BEGIN
    SELECT COUNT(*) FROM pg_class WHERE relname=cSequenceName::name AND
relkind=''S'' INTO nCnt;
    IF nCnt>0 THEN
     IF char_length(cSQLTrue) THEN
      EXECUTE cSQLTrue;
     END IF;
        RETURN true;
    ELSE
     IF char_length(cSQLFalse) THEN
      EXECUTE cSQLFalse;
     END IF;
        RETURN true;
    END IF;
END;'
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION iif_exist_sequence(varchar,varchar,varchar) IS 'Execute
first expression if sequence exist, second expression if sequence does not
exist';
----------------------------------------------------------------------------
----------