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
----- Original Message -----
From: "Vincent Stoessel"
Is there an sql query that I can use on postgres that will tell
me if a table exists?
select relname
from pg_class
where relowner = (select usesysid
from pg_user
where usename='USERNAME')
and relkind='r';
...will give u the list of all tables owned by user USERNAME
so something like:
select count(*)
from pg_class
where relname = 'TABLENAME'
and relkind='r'
and relowner = (select usesysid
from pg_user
where usename='USERNAME');
...will return 0 if the table does not exist
hth,
Marin
----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "
Vincent Stoessel writes:
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.
Something like:
SELECT COUNT(relname) FROM pg_class WHERE relname = 'tablename';
Regards, Lee Kindness
On Mon, 2002-05-13 at 14:33, Vincent Stoessel wrote:
Is there an sql query that I can use on postgres that will tell
me if a table exists?
SELECT relname
FROM pg_class
WHERE relname = 'your_table' AND relkind = 'r';
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Watch ye and pray, lest ye enter into temptation. The
spirit truly is ready, but the flesh is weak."
Mark 14:38
On Mon, 13 May 2002, Vincent Stoessel wrote:
Is there an sql query that I can use on postgres that will tell
me if a table exists?
SELECT COUNT(1) FROM pg_tables WHERE tablename='whatever';
For more information, type \dS in your psql client. It will show you
the many system tables that contain information and various sundry
things about your postgres database/installation.
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Administrator |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas@townnews.com AIM : trifthen |
| Web : www.townnews.com |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
Vincent Stoessel <vincent@xaymaca.com> writes:
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.
Run 'psql -E', do a \dt, and it will show you the queries it uses to
enumerate the tables.
--
Dave Carrigan (dave@rudedog.org) | Yow! My Aunt MAUREEN was a
UNIX-Apache-Perl-Linux-Firewalls-LDAP-C-DNS | military advisor to IKE & TINA
Seattle, WA, USA | TURNER!!
http://www.rudedog.org/ |
On Mon, 13 May 2002, Vincent Stoessel wrote:
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.
select 1 from pg_tables where tablename='tablethatmayexist';
If you get back a 1, it exists, if you get back no rows, it doesn't.
----- 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';
----------------------------------------------------------------------------
----------
