----- 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';
----------------------------------------------------------------------------
----------
