Re: sending a block through jdbc

On 1/05/2010 11:20 AM, Maxime Lévesque wrote:
> I want to drop a constraint if it exists, and not get an error if it
> doesn't exist

Check the system tables / INFORMATION SCHEMA to test if it exists before
dropping it.

> ideally I'd get a jdbc error code, and silence the 'consrtaint does
> not exist' exception,
> but since postgress doesn't send error codes to jdbc, I'd like to put
> the statement in

Yes, it does. As Kris noted, it's in SQLException. Use the "getSQLState"
method. It's often useful to test for prefixes.

http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/JDBC20.html#JDBC209

http://www.postgresql.org/docs/current/interactive/errcodes-appendix.html

> a block and silence the exception like this
>
> sb.append("begin \n");
> sb.append("alter table " + foreingKeyTable.name + " drop
> constraint " + fkName + ";\n");
> sb.append("exception when true then \n");

Exception handlers are a part of the PL/PgSQL language. They're not
supported in SQL, and don't make much sense there since there's no
procedural logic flow.

Kris pointed out that something similar is possible in 9.0. Consider
whether it's actually a good idea, though. DO blocks won't be cheap to
execute - especially compared to plain old SQL.

--
Craig Ringer