From: Josh Berkus <josh@agliodbs.com>
To: Julester <cubalibr@optonline.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Checking for table existence
Date: Mon, 17 Sep 2001 20:54:23 -0700
Message-ID: <web-120865@davinci.ethosmedia.com> (raw)
In-Reply-To: <prso7.50245$f7.6232115@news02.optonline.net>
Julester,
> Hi everyone. In my old SQL Server days, I used a command such as "IF
> exists(select name from sys_objects where name = 'xyztable')" to
> check if a
> table existed before creating it with a standard CREATE command. I
> looked
> in the PostgreSQL documentation, but for the life of me, I can't find
> an
> equivalent. I can view if the table exists by doing a select against
> the
> meta-data tables, but what about the IF statement ? Any help would
> be
> greatly appreciated. Thanks.
<grin> You've gotten a lot of complex answers to a simple question.
Confused yet?
If you're doing this in PL/pgSQL, you want a couple of functions:
(Hey Roberto, how about posting the 1st function on your site?)
CREATE FUNCTON table_exists(
VARCHAR ) RETURNS BOOLEAN AS '
DECLARE
t_name ALIAS for $1;
t_result VARCHAR;
BEGIN
--find table, case-insensitive
SELECT relname INTO t_result
FROM pg_class
WHERE relname ~* (''^'' || t_name || ''$'')
AND relkind = 'r';
IF t_result IS NULL THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;'
LANGUAGE 'plpgsql';
... then you build your function around this:
CREATE FUNCTION my_function ( ...
...
IF NOT table_exists(''my_table'') THEN
CREATE TABLE ...
END IF;
...
Got the idea?
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
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: josh@agliodbs.com, cubalibr@optonline.net
Subject: Re: Checking for table existence
In-Reply-To: <web-120865@davinci.ethosmedia.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