Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8I3sRd14089 for ; Mon, 17 Sep 2001 23:54:27 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 120865; Mon, 17 Sep 2001 20:54:23 -0700 From: "Josh Berkus" Subject: Re: Checking for table existence To: "Julester" Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 17 Sep 2001 20:54:23 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===120865====davinci.ethosmedia.com===_" X-Archive-Number: 200109/121 X-Sequence-Number: 4792 This is a multi-part MIME message --_===120865====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit 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. 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 --_===120865====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===120865====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===120865====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===120865====davinci.ethosmedia.com===_--