X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 5B361B430D3 for ; Mon, 7 Jul 2003 13:58:51 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 82777-02 for ; Mon, 7 Jul 2003 10:58:39 -0300 (ADT) Received: from www.bowmansystems.com (unknown [65.169.9.145]) by svr1.postgresql.org (Postfix) with ESMTP id 12537B43170 for ; Mon, 7 Jul 2003 10:58:39 -0300 (ADT) Received: from [10.0.3.3] (unknown [65.169.9.134]) by www.bowmansystems.com (Postfix) with ESMTP id C1CB8CDCB for ; Mon, 7 Jul 2003 08:52:08 -0500 (CDT) Subject: Database Upgrade scripts (AKA Conditional SQL execution) From: Richard Rowell To: pgsql-sql@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-szUBr4LtzsIbXpdbgvXO" Message-Id: <1057586322.31898.18.camel@localhost> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.3.92 (Preview Release) Date: 07 Jul 2003 08:58:42 -0500 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/63 X-Sequence-Number: 14177 --=-szUBr4LtzsIbXpdbgvXO Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Moving to Postgres from MS SQL server wherever I can. When writing database upgrade scripts, it is nice to construct the script so it will run correctly even if run twice. In MS-SQL's TSQL I would do something like this: IF( SELECT COUNT(*) FROM sysobjects WHERE name =3D 'foo' AND type =3D'U' ) < 1 THEN CREATE TABLE foo() END Can I accomplish this with postgresql without involving an external process (like say perl)? I guess I could put the upgrade stuff into PL/SQL functions and just drop the functions when I'm done, but I was hoping for something a little "cleaner". --=20 Richard Rowell --=-szUBr4LtzsIbXpdbgvXO Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQA/CXySI1rv5HoOjmARAoBdAJ9E9dujKRAxuxT1YBys2rP5B+87CQCgr+Wr /LdwSnmBytH7GuEu13ey87Y= =tvZr -----END PGP SIGNATURE----- --=-szUBr4LtzsIbXpdbgvXO--